Thread: BUG #10171: Specific prepared statement cannot use bitmapOr index scan since 9.2

The following bug has been logged on the website:

Bug reference:      10171
Logged by:          Arnold Scheffler
Email address:      pilum.70@uni-muenster.de
PostgreSQL version: 9.3.4
Operating system:   CentOS 5/6, enough memory
Description:

Migrating WebCMS Imperia to PostgreSQL 9.3 revealed a significant
Performance Decrease
(query took 4s instead of .4 ms)

The following query uses bitmap or index scan in 9.1, but only Nested Loop
in
9.2 and 9.3:

Steps to reproduce without Imperia CMS and with simplified query:

create table t1 as
select generate_series as id1, chr( cast( ceil(74*RANDOM()+48) as integer))
||chr( cast( ceil(74*RANDOM()+48) as integer))
||chr( cast( ceil(74*RANDOM()+48) as integer))
||chr( cast( ceil(74*RANDOM()+48) as integer))
||chr( cast( ceil(74*RANDOM()+48) as integer))
||chr( cast( ceil(74*RANDOM()+48) as integer))
||chr( cast( ceil(74*RANDOM()+48) as integer))
as id2
from generate_series(1,100000);
create table t2 as
select generate_series as id1, chr( cast( ceil(74*RANDOM()+48) as integer))
||chr( cast( ceil(74*RANDOM()+48) as integer))
||chr( cast( ceil(74*RANDOM()+48) as integer))
||chr( cast( ceil(74*RANDOM()+48) as integer))
||chr( cast( ceil(74*RANDOM()+48) as integer))
||chr( cast( ceil(74*RANDOM()+48) as integer))
||chr( cast( ceil(74*RANDOM()+48) as integer))
 as id2
from generate_series(1,200);
create unique index id11_idx on t1(id1);
create unique index id12_idx on t1(id2);
create unique index id21_idx on t2(id1);
create unique index id22_idx on t2(id2);
prepare slowquery as
SELECT t1.id1, t2.id1
FROM t1 JOIN t2
             ON (t1.id2, t2.id2)
             IN (($1, $2),
                 ($3, $4));
explain analyze execute slowquery('','',NULL,NULL);

pilum.70@uni-muenster.de writes:
> The following query uses bitmap or index scan in 9.1, but only Nested Loop
> in 9.2 and 9.3:

Interesting.  The plan actually gets stupider when it has values for the
parameters, if those values chance to be NULL.

The reason seems to be that after substituting the parameters and
constant-folding, we've got this join condition:

(t1.id2 = ''::text AND t2.id2 = ''::text) OR NULL::boolean

and the code in orclause.c is unable to realize that it can extract the
sub-AND elements as restriction clauses for t1 and t2.  But it'd be
all right to ignore the constant-null OR arm, since that can't possibly
succeed.

Another way of looking at this is that we should be smart enough to throw
away the constant-null OR arm altogether at top level of WHERE, since at
top level we don't particularly care whether the OR produces FALSE or NULL
if all its other inputs are FALSE.  That would probably be a more general
fix.

I'll see what I can do about this.  Thanks for the report!

            regards, tom lane