BUG #10171: Specific prepared statement cannot use bitmapOr index scan since 9.2 - Mailing list pgsql-bugs

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);