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

On Thu, Jul 30, 2015 at 6:53 PM, <marko(at)joh(dot)to> wrote:

> 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?
>

​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/>

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(dot)boguk(at)gmail(dot)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."