Apply LIMIT when computation is logically irrelevant - Mailing list pgsql-general

Hi,

When an SQL needs to UNION constants on either side, it should be possible to 

implicitly apply a LIMIT 1 and get good speed up. Is this an incorrect understanding,

or something already discussed but rejected for some reason?

This need came up while reviewing generated SQL, where the need was to return true when

at least one of two lists had a row. A simplified version is given below:

(SELECT 1 FROM pg_class) UNION (SELECT 1 FROM pg_class);
vs.
(select 1 FROM pg_class limit 1) UNION (SELECT 1 FROM pg_class limit 1); -- Faster




postgres=# explain analyse (select 1 from generate_series(1,10000)) UNION (select 1 from generate_series(1,10000));
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=550.00..750.00 rows=20000 width=4) (actual time=54.847..54.866 rows=1 loops=1)
   Group Key: (1)
   ->  Append  (cost=0.00..500.00 rows=20000 width=4) (actual time=0.782..40.215 rows=20000 loops=1)
         ->  Function Scan on generate_series  (cost=0.00..100.00 rows=10000 width=4) (actual time=0.780..7.542 rows=10000 loops=1)
         ->  Function Scan on generate_series generate_series_1  (cost=0.00..100.00 rows=10000 width=4) (actual time=0.929..7.706 rows=10000 loops=1)
 Planning Time: 0.055 ms
 Execution Time: 55.535 ms
(7 rows)

postgres=# explain analyse (select 1 from generate_series(1,10000) limit 1) UNION (select 1 from generate_series(1,10000) limit 1);
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=0.07..0.08 rows=2 width=4) (actual time=1.292..1.298 rows=1 loops=1)
   ->  Sort  (cost=0.07..0.07 rows=2 width=4) (actual time=1.290..1.292 rows=2 loops=1)
         Sort Key: (1)
         Sort Method: quicksort  Memory: 25kB
         ->  Append  (cost=0.00..0.06 rows=2 width=4) (actual time=0.554..1.266 rows=2 loops=1)
               ->  Limit  (cost=0.00..0.01 rows=1 width=4) (actual time=0.552..0.554 rows=1 loops=1)
                     ->  Function Scan on generate_series  (cost=0.00..100.00 rows=10000 width=4) (actual time=0.550..0.551 rows=1 loops=1)
               ->  Limit  (cost=0.00..0.01 rows=1 width=4) (actual time=0.706..0.707 rows=1 loops=1)
                     ->  Function Scan on generate_series generate_series_1  (cost=0.00..100.00 rows=10000 width=4) (actual time=0.704..0.705 rows=1 loops=1)
 Planning Time: 0.096 ms
 Execution Time: 1.847 ms
(11 rows)


postgres=# select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 13devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.2.1 20170915 (Red Hat 7.2.1-2), 64-bit
(1 row)

-
robins