Re: Multi - table statistics

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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828