Re: Select time jump after adding filter; please help me figure out what I'm doing wrong.

Andrew Edson <cheighlund(at)yahoo(dot)com> writes:
> This problem may have already been solved; I'm using an older
> version of Postgres; 8.1.3.

Ah. I think your result is explained by this 8.1.4 bug fix:

2006-05-18 14:57 tgl

* src/backend/optimizer/plan/createplan.c (REL8_1_STABLE): When a
bitmap indexscan is using a partial index, it is necessary to
include the partial index predicate in the scan's "recheck
condition". Otherwise, if the scan becomes lossy for lack of
bitmap memory, we would fail to enforce that returned rows satisfy
the predicate. Noted while studying bug #2441 from Arjen van der
Meijden.

IOW, once the bitmap goes lossy, we'd return *every* row on any page
that the index fingered as having *any* relevant row.

> My boss has requested that it not be
> upgraded just yet, however, so I'm stuck with it for the moment.

Better press him a little harder. There are a lot of known bugs in
8.1.3, and not any very good reason not to update to a later 8.1.x.

As a really short-term measure, you could possibly avoid this bug by
increasing work_mem enough that the bitmap doesn't get compressed.

regards, tom lane