BUG #13528: LATERAL vs. correlated scalar subquery

From: marko(at)joh(dot)to
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13528: LATERAL vs. correlated scalar subquery
Date: 2015-07-30 08:53:52
Message-ID: 20150730085352.9098.47740@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13528
Logged by: Marko Tiikkaja
Email address: marko(at)joh(dot)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?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jack Douglas 2015-07-30 08:56:45 Re: BUG #13523: Unexplained deadlocks (possible race condition)
Previous Message Andres Freund 2015-07-30 08:38:05 Re: BUG #13523: Unexplained deadlocks (possible race condition)