I'm using postgres 7.4 and having a problem with the query optimizer. Our table,
T, looks like this:
dh int
fh int
nm int
... -- other columns
A typical row is 400-500 bytes.
T has two indexes, idx_df on (dh, fh) and idx_dn on (dh, nm).
My query is
select * from T
where dh = ? and fh = ?
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
And if I use 1057692240 in the EXPLAIN, I get the desired plan:
Index Scan using idx_df on external_file (cost=0.00..5.27 rows=1 width=561)
Index Cond: ((dir_hash = 1057692240) AND (fn_hash = 2))
I've tried playing with various cost settings (e.g. random_page_cost), but have
been unable to influence the optimizer's behavior. Rewriting the query as ...
where (dh, fh) = (?, ?) doesn't help.
So a few questions:
- Why would the optimizer ever choose idx_dn over idx_df given that idx_df has
to be more selective?
- Is there any way to force the use of idx_df?
Jack Orenstein
P.S. Yes, I know, 7.4. We're upgrading to 8.3 but we have this problem right now.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
