The following bug has been logged on the website:
Bug reference: 13528
Logged by: Marko Tiikkaja
Email address: marko@joh.to
PostgreSQL version: 9.4.4
Operating system: Linux
Description:
Hi,
Observe the following case:
=# create table data(a int, b int, primary key(a,b));
CREATE TABLE
=# insert into data select i, random() * 100 from generate_series(1, 100000)
i;
INSERT 0 100000
=# create view counts as select a, count(*) from data group by a;
CREATE VIEW
=# explain analyze select u.elem, x.count from unnest(array[1]) u(elem),
lateral (select counts.count from counts where counts.a = u.elem) x;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1867.28..1873.03 rows=100 width=12) (actual
time=69.858..77.021 rows=1 loops=1)
Hash Cond: (data.a = u.elem)
-> HashAggregate (cost=1865.03..1867.03 rows=200 width=4) (actual
time=44.528..70.394 rows=100000 loops=1)
Group Key: data.a
-> Seq Scan on data (cost=0.00..1391.02 rows=94802 width=4)
(actual time=0.013..8.586 rows=100000 loops=1)
-> Hash (cost=1.00..1.00 rows=100 width=4) (actual time=0.012..0.012
rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Function Scan on unnest u (cost=0.00..1.00 rows=100 width=4)
(actual time=0.010..0.011 rows=1 loops=1)
Planning time: 0.142 ms
Execution time: 77.551 ms
(10 rows)
Tweaking any of the enable_* parameters doesn't get me to the desired query
produced by the old way of LATERALizing:
=# explain analyze select u.elem, (select counts.count from counts where
counts.a = u.elem) from unnest(array[1]) u(elem);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Function Scan on unnest u (cost=0.00..125498.75 rows=100 width=4) (actual
time=0.037..0.038 rows=1 loops=1)
SubPlan 1
-> Subquery Scan on counts (cost=0.29..1254.98 rows=1 width=8)
(actual time=0.024..0.024 rows=1 loops=1)
-> GroupAggregate (cost=0.29..1254.97 rows=1 width=4) (actual
time=0.023..0.023 rows=1 loops=1)
Group Key: data.a
-> Index Only Scan using data_pkey on data
(cost=0.29..1252.59 rows=474 width=4) (actual time=0.017..0.018 rows=1
loops=1)
Index Cond: (a = u.elem)
Heap Fetches: 1
Planning time: 0.125 ms
Execution time: 0.073 ms
(10 rows)
Is there some fundamental issue here which prevents the planner from
producing the same plan?