Thread
-
BUG #14746: sub query's plan not right?
digoal@126.com — 2017-07-17T03:17:58Z
The following bug has been logged on the website: Bug reference: 14746 Logged by: Zhou Digoal Email address: digoal@126.com PostgreSQL version: 10beta2 Operating system: CentOS 6.x x64 Description: HI, there is something wrong for sub query. i have two table, a and b, a is the metadata, b is for feed data. ``` create table a (id int primary key); create table b(aid int, crt_time timestamp, val numeric); create index idx_b_1 on b(aid, crt_time desc); insert into a select generate_series(0,100000); insert into b select random()*100000, clock_timestamp(), random() from generate_series(1,10000000); ``` wrong example: ``` postgres=# explain select (t.b).aid,(t.b).val,(t.b).crt_time from ( select ( select b from b where b.aid=a.id order by crt_time desc limit 1 ) from a ) t where (t.b).aid is not null; QUERY PLAN ---------------------------------------------------------------------------------------- Seq Scan on a (cost=0.00..756247.09 rows=99500 width=44) Filter: (((SubPlan 4)).aid IS NOT NULL) SubPlan 4 -> Limit (cost=0.57..1.89 rows=1 width=55) -> Index Scan using idx_b_1 on b b_3 (cost=0.57..946.44 rows=713 width=55) Index Cond: (aid = a.id) SubPlan 1 -> Limit (cost=0.57..1.89 rows=1 width=55) -> Index Scan using idx_b_1 on b (cost=0.57..946.44 rows=713 width=55) Index Cond: (aid = a.id) SubPlan 2 -> Limit (cost=0.57..1.89 rows=1 width=55) -> Index Scan using idx_b_1 on b b_1 (cost=0.57..946.44 rows=713 width=55) Index Cond: (aid = a.id) SubPlan 3 -> Limit (cost=0.57..1.89 rows=1 width=55) -> Index Scan using idx_b_1 on b b_2 (cost=0.57..946.44 rows=713 width=55) Index Cond: (aid = a.id) (18 rows) ``` when i use limit for a scan, it's correct ``` postgres=# explain select (t.b).aid,(t.b).val,(t.b).crt_time from ( select ( select b from b where b.aid=a.id order by crt_time desc limit 1 ) from a limit 100001 ) t where (t.b).aid is not null; QUERY PLAN ------------------------------------------------------------------------------------------------ Subquery Scan on t (cost=0.00..191854.32 rows=99500 width=44) Filter: ((t.b).aid IS NOT NULL) -> Limit (cost=0.00..190854.32 rows=100000 width=32) -> Seq Scan on a (cost=0.00..190854.32 rows=100000 width=32) SubPlan 1 -> Limit (cost=0.57..1.89 rows=1 width=55) -> Index Scan using idx_b_1 on b (cost=0.57..946.44 rows=713 width=55) Index Cond: (aid = a.id) (8 rows) ``` the query run time is alse fast when i use limit for a. ``` postgres=# explain analyze select (t.b).aid,(t.b).val,(t.b).crt_time from ( select ( select b from b where b.aid=a.id order by crt_time desc limit 1 ) from a limit 100001 ) t where (t.b).aid is not null; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan on t (cost=0.00..191854.32 rows=99500 width=44) (actual time=0.053..814.481 rows=100000 loops=1) Filter: ((t.b).aid IS NOT NULL) -> Limit (cost=0.00..190854.32 rows=100000 width=32) (actual time=0.050..787.891 rows=100000 loops=1) -> Seq Scan on a (cost=0.00..190854.32 rows=100000 width=32) (actual time=0.049..780.356 rows=100000 loops=1) SubPlan 1 -> Limit (cost=0.57..1.89 rows=1 width=55) (actual time=0.007..0.007 rows=1 loops=100000) -> Index Scan using idx_b_1 on b (cost=0.57..946.44 rows=713 width=55) (actual time=0.007..0.007 rows=1 loops=100000) Index Cond: (aid = a.id) Planning time: 0.145 ms Execution time: 818.490 ms (10 rows) postgres=# explain analyze select (t.b).aid,(t.b).val,(t.b).crt_time from ( select ( select b from b where b.aid=a.id order by crt_time desc limit 1 ) from a ) t where (t.b).aid is not null; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on a (cost=0.00..756247.09 rows=99500 width=44) (actual time=0.081..2290.582 rows=100000 loops=1) Filter: (((SubPlan 4)).aid IS NOT NULL) SubPlan 4 -> Limit (cost=0.57..1.89 rows=1 width=55) (actual time=0.007..0.007 rows=1 loops=100000) -> Index Scan using idx_b_1 on b b_3 (cost=0.57..946.44 rows=713 width=55) (actual time=0.007..0.007 rows=1 loops=100000) Index Cond: (aid = a.id) SubPlan 1 -> Limit (cost=0.57..1.89 rows=1 width=55) (actual time=0.005..0.005 rows=1 loops=100000) -> Index Scan using idx_b_1 on b (cost=0.57..946.44 rows=713 width=55) (actual time=0.004..0.004 rows=1 loops=100000) Index Cond: (aid = a.id) SubPlan 2 -> Limit (cost=0.57..1.89 rows=1 width=55) (actual time=0.005..0.005 rows=1 loops=100000) -> Index Scan using idx_b_1 on b b_1 (cost=0.57..946.44 rows=713 width=55) (actual time=0.004..0.004 rows=1 loops=100000) Index Cond: (aid = a.id) SubPlan 3 -> Limit (cost=0.57..1.89 rows=1 width=55) (actual time=0.005..0.005 rows=1 loops=100000) -> Index Scan using idx_b_1 on b b_2 (cost=0.57..946.44 rows=713 width=55) (actual time=0.004..0.004 rows=1 loops=100000) Index Cond: (aid = a.id) Planning time: 0.238 ms Execution time: 2294.653 ms (20 rows) ``` is this a bug ? or it can be improved? thank you, best regards, digoal -
Re: BUG #14746: sub query's plan not right?
Feike Steenbergen <feikesteenbergen@gmail.com> — 2017-07-26T08:58:59Z
> is this a bug ? or it can be improved? Perhaps, for me it rings a bell of using OFFSET 0 as a hack, that at least has the property of not changing the logic of your query: select (t.b).aid,(t.b).val,(t.b).crt_time from ( select ( select b from b where b.aid=a.id order by crt_time desc limit 1 ) from a offset 0 ) t where (t.b).aid is not null; Previous similar discussion: https://www.postgresql.org/message-id/E1RfAwz-0006Us-7B%40wrigleys.postgresql.org Some other pointers: https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/ https://stackoverflow.com/questions/14897816/how-can-i-prevent-postgres-from-inlining-a-subquery