Обсуждение: BUG #10171: Specific prepared statement cannot use bitmapOr index scan since 9.2
BUG #10171: Specific prepared statement cannot use bitmapOr index scan since 9.2
От
pilum.70@uni-muenster.de
Дата:
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