Regression from 8.4 to 9.1.2/9.1.3: Optimizing filters on constants in unions

Поиск
Список
Период
Сортировка
От Claus Stadler
Тема Regression from 8.4 to 9.1.2/9.1.3: Optimizing filters on constants in unions
Дата
Msg-id 4F516CC4.9020606@informatik.uni-leipzig.de
обсуждение исходный текст
Ответы Re: Regression from 8.4 to 9.1.2/9.1.3: Optimizing filters on constants in unions  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Regression from 8.4 to 9.1.2/9.1.3: Optimizing filters on constants in unions  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Hi, not sure if this bug is already known, so sorry if it is ;)

Filtering a Union on constant custom type:
-------------------------------------------------------------------------------
DROP VIEW v;
DROP TABLE a;
DROP TABLE b;

DROP TYPE IF EXISTS mytype;
CREATE TYPE mytype AS ENUM ('x', 'y');

CREATE TABLE a (id INT);
CREATE TABLE b (id INT);

CREATE VIEW v AS SELECT t, id FROM (SELECT 'x'::mytype t, id FROM a
UNION ALL SELECT 'y'::mytype t, id FROM b) c;

EXPLAIN SELECT * FROM v WHERE t = 'y';


PostgreSQL 8.4.10 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.5.real
(Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit
  Result  (cost=0.00..34.00 rows=2400 width=8)
    ->  Append  (cost=0.00..34.00 rows=2400 width=8)
          ->  Seq Scan on b  (cost=0.00..34.00 rows=2400 width=8)


PostgreSQL 9.1.2 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit and also
PostgreSQL 9.1.3 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
  Result  (cost=0.00..80.00 rows=24 width=8)
    ->  Append  (cost=0.00..80.00 rows=24 width=8)
          ->  Seq Scan on a  (cost=0.00..40.00 rows=12 width=8)
                Filter: ('x'::mytype = 'y'::mytype)
          ->  Seq Scan on b  (cost=0.00..40.00 rows=12 width=8)
                Filter: ('y'::mytype = 'y'::mytype)

Regression: Expected result: That of 8.4.10
=====================================

Additionally, it is interesting that it does not work with text (not
tested with other types such as integers):
-------------------------------------------------------------------------------

DROP VIEW v;
DROP TABLE a;
DROP TABLE b;

CREATE TABLE a (id INT);
CREATE TABLE b (id INT);

CREATE VIEW v AS SELECT t, id FROM (SELECT 'x' t, id FROM a UNION ALL
SELECT 'y' t, id FROM b) c;

EXPLAIN SELECT * FROM v WHERE t = 'y';

PostgreSQL 8.4.10 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.5.real
(Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit
  Subquery Scan d  (cost=0.00..1107744.06 rows=150000 width=36)
    Filter: (d.t = 'y'::text)
    ->  Append  (cost=0.00..732744.04 rows=30000002 width=4)
          ->  Subquery Scan "*SELECT* 1"  (cost=0.00..244247.54
rows=9999977 width=4)
                ->  Seq Scan on a  (cost=0.00..144247.77 rows=9999977
width=4)
          ->  Subquery Scan "*SELECT* 2"  (cost=0.00..244248.96
rows=10000048 width=4)
                ->  Seq Scan on b  (cost=0.00..144248.48 rows=10000048
width=4)
          ->  Subquery Scan "*SELECT* 3"  (cost=0.00..244247.54
rows=9999977 width=4)
                ->  Seq Scan on c  (cost=0.00..144247.77 rows=9999977
width=4)


PostgreSQL 9.1.2 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit and also
PostgreSQL 9.1.3 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
  Result  (cost=0.00..80.00 rows=24 width=36)
    ->  Append  (cost=0.00..80.00 rows=24 width=36)
          ->  Seq Scan on a  (cost=0.00..40.00 rows=12 width=36)
                Filter: ('x'::text = 'y'::text)
          ->  Seq Scan on b  (cost=0.00..40.00 rows=12 width=36)
                Filter: ('y'::text = 'y'::text)


Expected: Query optimizer should discard the scan as filter cannot be
satisfied.
                Filter: ('x'::text = 'y'::text)
================================================================================

В списке pgsql-bugs по дате отправления:

Предыдущее
От: "Kevin Grittner"
Дата:
Сообщение: Re: BUG #6503: Idle in transaction while lazy loading in JSF render response
Следующее
От: Claus Stadler
Дата:
Сообщение: Regression from 8.4 to 9.1.2/9.1.3: Optimizing filters on constants in unions