BUG #13528: LATERAL vs. correlated scalar subquery
От | marko@joh.to |
---|---|
Тема | BUG #13528: LATERAL vs. correlated scalar subquery |
Дата | |
Msg-id | 20150730085352.9098.47740@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #13528: LATERAL vs. correlated scalar subquery
|
Список | pgsql-bugs |
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?
В списке pgsql-bugs по дате отправления: