Re: Yet another optimizer index choosing questions

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(at)nextbus(dot)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(at)nextbus(dot)com
(510)420-3120
www.nextbus.com
spinning to infinity, hallelujah
--------------------------------------------------------