Jack Orenstein <jack.orenstein@xxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
