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
