BUG #13528: LATERAL vs. correlated scalar subquery

On Thu, Jul 30, 2015 at 6:53 PM, <marko@joh.to> wrote:

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?

​Hi Marko,

You could see that the new plan have lower total cost than the old plan
(​cost=1867.28..1873.03 vs ​cost=0.00..125498.75).
I think it's primary reason why it been selected (planner could produce the
old plan but new plan wins on the cost basis).
An fundamental issue there that unnest expected return 100 rows by default
instead of the 1 row in your case.
( ​Function Scan on unnest u (cost=0.00..1.00 rows=100 width=4) vs (actual
time=0.010..0.011 rows=1 loops=1) )
If you try the same test with 100 element array I think the new
plan/lateral query will be faster than the old.
It's somewhat fundamental issue with planning queries with unnest(constant
array) (i been burned by it many times).

--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/ <http://www.postgresql-consulting.com/&gt;

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com
МойКруг: http://mboguk.moikrug.ru/

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."

On 7/30/15 1:48 PM, Maxim Boguk wrote:

You could see that the new plan have lower total cost than the old plan
(​cost=1867.28..1873.03 vs ​cost=0.00..125498.75).
I think it's primary reason why it been selected (planner could produce the
old plan but new plan wins on the cost basis).

I'll have to admit I could've put more time into the original report,
but I don't think that's accurate. If I disable hashagg and hashjoin
and tune the query to tell the planner that only one row is to be
expected, the plan looks like this:

=#* explain analyze select u.elem, x.count from (SELECT u.elem FROM
unnest(array[1]) u(elem) LIMIT 1) u(elem), lateral (select counts.count
from counts where counts.a = u.elem) x;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.29..4778.86 rows=1 width=12) (actual
time=0.060..52.380 rows=1 loops=1)
Join Filter: (u.elem = data.a)
Rows Removed by Join Filter: 99999
-> Limit (cost=0.00..0.01 rows=1 width=4) (actual
time=0.010..0.011 rows=1 loops=1)
-> Function Scan on unnest u (cost=0.00..1.00 rows=100
width=4) (actual time=0.009..0.009 rows=1 loops=1)
-> GroupAggregate (cost=0.29..4774.33 rows=200 width=4) (actual
time=0.047..45.634 rows=100000 loops=1)
Group Key: data.a
-> Index Only Scan using data_pkey on data
(cost=0.29..4298.32 rows=94802 width=4) (actual time=0.042..19.381
rows=100000 loops=1)
Heap Fetches: 100000
Planning time: 0.147 ms
Execution time: 52.429 ms
(11 rows)

which to me suggests that the planner just doesn't realize that it can
push the condition on counts.a into the view.

.m

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Marko Tiikkaja <marko@joh.to> writes:

On 7/30/15 1:48 PM, Maxim Boguk wrote:

You could see that the new plan have lower total cost than the old plan
(​cost=1867.28..1873.03 vs ​cost=0.00..125498.75).
I think it's primary reason why it been selected (planner could produce the
old plan but new plan wins on the cost basis).

I'll have to admit I could've put more time into the original report,
but I don't think that's accurate.

Yeah. It would be nice if we could produce a more accurate rowcount
estimate for unnest(array[...]); that's something that's been a pain
for Salesforce so I've been considering ways to fix it. But it's
not the killer problem here.

which to me suggests that the planner just doesn't realize that it can
push the condition on counts.a into the view.

It can't. We'd need parameterized paths for subqueries, which we don't
have (yet).

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs