Re: BUG #13528: LATERAL vs. correlated scalar subquery

From: Marko Tiikkaja <marko(at)joh(dot)to>
To: Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com>
Cc: pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #13528: LATERAL vs. correlated scalar subquery
Date: 2015-07-30 12:24:47
Message-ID: 55BA178F.9050503@joh.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message igor 2015-07-30 14:39:21 BUG #13529: Incorrect work UPPER UTF-8, 9.2 was all right
Previous Message Maxim Boguk 2015-07-30 11:48:12 Re: BUG #13528: LATERAL vs. correlated scalar subquery