Re: Postgres optimizer choosing wrong index

Jack Orenstein <jack(dot)orenstein(at)hds(dot)com> writes:
> If I run EXPLAIN on this query, (plugging in values 1 and 2 for the variables),
> before VACUUM ANALYZE, I get the desired execution plan:

> Index Scan using idx_df on T (cost=0.00..4.83 rows=1 width=454)
> Index Cond: ((dh = 1) AND (fh = 2))

> But after VACUUM ANALYZE:

> Index Scan using idx_dn on T (cost=0.00..5.27 rows=1 width=561)
> Index Cond: (dh = 1)
> Filter: (fh = 2)

> Notice that postgres is now using the other index. This behavior is somewhat
> dependent on the values plugged in. I ran a query to count dh values:

> select dir_hash, count(*) from external_file group by dir_hash;

> dh | count
> ------------+--------
> 916645488 | 20000
> 1057692240 | 200000

So you're plugging in a value that doesn't appear in the index, and
Postgres knows it?

I think that in this example, the estimated costs are going to be
exactly the same either way, so it's kind of a tossup which index
gets chosen --- and it's not actually going to matter any at runtime
either. Descending the btree to find that there's no matching entry
is going to take just about the same amount of time in either index.
If you plug in a value that *does* occur in the table it should probably
choose the more-relevant index consistently.

regards, tom lane