Re: Query performance with disabled hashjoin and mergejoin

On Tue, Mar 8, 2011 at 4:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> The reason I thought cross-column correlations might be relevant is
>> that the bitmap index scan on news_visible_from is quite accurate
>> (19976 estimated vs. 19932 actual) and the bitmap index scan on
>> news_visible_to is tolerably accurate (151 estimated vs. 41 actual)
>> but the estimate on the BitmapOr is somehow totally wrong (20127
>> estimated vs. 0 actual).  But on further reflection that doesn't make
>> much sense.  How can the BitmapOr produce fewer rows than the sum of
>> its constituent inputs?
>
> That's not an estimation bug, that's a measurement bug.  We don't try to
> count the actual number of rows present in the result of a BitmapOr or
> BitmapAnd node.  (It would be impractical in lossy cases anyway, not to
> mention expensive.)

Mmm, OK. But I still think there's a problem with the selectivity
estimate in there somewhere, because

-> Bitmap Heap Scan on news
(cost=1282.94..5494.05 rows=1422 width=634) (actual time=5.532..5.560
rows=7 loops=1)

...which may be why the planner is going wrong for the OP.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company