On Jul 1, 2009, at 4:15 AM, Scara Maccai wrote:
I query is using Index scan instead of Bitmap Index Scan because it
"thinks" that the number of rows returned will be low (12). In
fact, the number of rows returned is 120.
This happens because there are some WHERE conditions that check
cross-tables values (something like taba.col1 < tabb.col2) . Since
Postgresql doesn't know (I guess) anything about cross-table values
statistics, how are the "rows" values calculated?
Are you doing this as a JOIN or a subquery? That could make a
difference. Also, non-equality predicates (ie: <) are hard to
estimate for.
Is there any way I can force the planner to use bitmap index scan?
I would try pulling the explicit values you need into a temp table,
analyze that, and then join. The planner will then know exactly how
many rows it's dealing with. But keep in mind that it doesn't
actually look at the values it will be getting, so if you have a
skewed distribution of values in the join column in the larger table
you might be stuck...
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@xxxxxxxxxxx
Give your computer some brain candy! www.distributed.net Team #1828
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
