Re: Yet another optimizer index choosing questions

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