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

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

Claus Stadler <cstadler@informatik.uni-leipzig.de> writes:
> Hi, not sure if this bug is already known, so sorry if it is ;)
> Filtering a Union on constant custom type:

This seems to be another unpleasant consequence of
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=57664ed25e5dea117158a2e663c29e60b3546e1c

I'm starting to think we need to revert that in favor of some other
solution, though I have no idea what yet.

            regards, tom lane

Claus Stadler <cstadler@informatik.uni-leipzig.de> writes:
> Filtering a Union on constant custom type:
> ...
> Expected: Query optimizer should discard the scan as filter cannot be
> satisfied.

I've applied a patch for this.  Thanks for the report!

            regards, tom lane

Cool, thank you very much.

Cheers,
Claus

On 03/16/2012 06:17 PM, Tom Lane wrote:
> Claus Stadler<cstadler@informatik.uni-leipzig.de>  writes:
>> Filtering a Union on constant custom type:
>> ...
>> Expected: Query optimizer should discard the scan as filter cannot be
>> satisfied.
> I've applied a patch for this.  Thanks for the report!
>
>             regards, tom lane
>