Re: Partial Index [WHERE col IN ('A','B')] Planner Anomalies - Mailing list pgsql-general

"David Johnston" <polobo@yahoo.com> writes:
> If I explain the above query multiple times (without any data changes) I get
> either of the two query plans / the Bitmap Heap one more often.  If I
> analyze the table I immediately get the "Index Scan" plan first but
> subsequent explains revert to switching between the two However, if I run
> any other query but the three-criteria query then the flipping behavior
> observed stops and the "Bitmap Heap" plan becomes the dominant plan.  The

Hmmm ... I'm seeing some very odd behavior here too.  I suspect there is
some use-of-uninitialized-memory in there.  After several false starts
I have a script that reliably reproduces the change of behavior in
(debug builds of) both HEAD and 9.0:

drop table if exists taskinstance;

create table taskinstance (ti_status varchar, ti_creationtimestamp timestamptz);
create index ti_sortedstatus on taskinstance (ti_status, ti_creationtimestamp);
create index ti_active on taskinstance (ti_status) WHERE ti_status IN ('ACTIVE', 'DISPATCHED', 'FAILURE');

vacuum taskinstance;

\c -

EXPLAIN SELECT * FROM taskinstance WHERE ti_status IN ('ACTIVE', 'DISPATCHED', 'FAILURE');
EXPLAIN SELECT * FROM taskinstance WHERE ti_status IN ('ACTIVE', 'DISPATCHED', 'FAILURE');
EXPLAIN SELECT * FROM taskinstance WHERE ti_status IN ('ACTIVE', 'DISPATCHED', 'FAILURE');

The first two EXPLAINs give a sane plan, the third one not so much.
Poking at it now.

            regards, tom lane