Обсуждение: Yet another optimizer index choosing questions

I have a four part primary key and only qualify
the query on the table against the first part of
the primary key.

When I do the query, it never uses the primary key
index.

Does postgres use partial indexes at all?
If so, the distribution of this one part of a primary
key is very even.  That is, each bucket has a
similar count.

I created a separate index on the first part of
the primary key and the query used it and cut down
the cost of the query significantly.

The table has about 50K rows but the
original query eats enough memory to choke postgres
(yes, yes, I'll throw more memory at it, too).
The query using the additional index works well.

Why did I have to create a second index?  Why
didn't it use a partial of the primary key index?

thanks,
elein

--
--------------------------------------------------------
elein@nextbus.com
(510)420-3120
www.nextbus.com
    spinning to infinity, hallelujah
--------------------------------------------------------

Elein <elein@nextbus.com> writes:
> I have a four part primary key and only qualify
> the query on the table against the first part of
> the primary key.

> When I do the query, it never uses the primary key
> index.

> Does postgres use partial indexes at all?

Sure.

regression=# create table foo (f1 int, f2 int, f3 int, primary key(f1,f2));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE
regression=# explain select * from foo where f1 = 42;
NOTICE:  QUERY PLAN:

Index Scan using foo_pkey on foo  (cost=0.00..17.07 rows=5 width=12)

EXPLAIN

> If so, the distribution of this one part of a primary
> key is very even.  That is, each bucket has a
> similar count.

And how big are the buckets?  If the first column alone is not very
selective, then the optimizer is not necessarily doing the wrong
thing here.

In general, questions about optimizer behavior that include no specific
details (like EXPLAIN outputs) are not likely to draw useful answers.

            regards, tom lane

On Thu, 24 Jan 2002, Elein wrote:

>
> I have a four part primary key and only qualify
> the query on the table against the first part of
> the primary key.
>
> When I do the query, it never uses the primary key
> index.
>
> Does postgres use partial indexes at all?
> If so, the distribution of this one part of a primary
> key is very even.  That is, each bucket has a
> similar count.
>
> I created a separate index on the first part of
> the primary key and the query used it and cut down
> the cost of the query significantly.
>
> The table has about 50K rows but the
> original query eats enough memory to choke postgres
> (yes, yes, I'll throw more memory at it, too).
> The query using the additional index works well.
>
> Why did I have to create a second index?  Why
> didn't it use a partial of the primary key index?

It should be willing to use the first columns of the index
up until the first column not used for the scan AFAIK, can you send
the creates for the objects in question, queries and explain output?

I think my problem has to do with old and bad statistics.
We ran into the vacuum analyze bug so we've been holding
off vacuum analyze until we can install the latest release.
The statics we have are more skewed than I thought causing
whacko query plans.

I have been able to see pg use the partial key index in
other more pristine databases so my question about the
ability to do so is answered by that and your response.

Thank you,
elein


Tom Lane wrote:

> Elein <elein@nextbus.com> writes:
>
>>I have a four part primary key and only qualify
>>the query on the table against the first part of
>>the primary key.
>>
>
>>When I do the query, it never uses the primary key
>>index.
>>
>
>>Does postgres use partial indexes at all?
>>
>
> Sure.
>
> regression=# create table foo (f1 int, f2 int, f3 int, primary key(f1,f2));
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
> CREATE
> regression=# explain select * from foo where f1 = 42;
> NOTICE:  QUERY PLAN:
>
> Index Scan using foo_pkey on foo  (cost=0.00..17.07 rows=5 width=12)
>
> EXPLAIN
>
>
>>If so, the distribution of this one part of a primary
>>key is very even.  That is, each bucket has a
>>similar count.
>>
>
> And how big are the buckets?  If the first column alone is not very
> selective, then the optimizer is not necessarily doing the wrong
> thing here.
>
> In general, questions about optimizer behavior that include no specific
> details (like EXPLAIN outputs) are not likely to draw useful answers.
>
>             regards, tom lane
>
>



--
--------------------------------------------------------
elein@nextbus.com
(510)420-3120
www.nextbus.com
    spinning to infinity, hallelujah
--------------------------------------------------------