BUG #14746: sub query's plan not right?
digoal@126.com
From: digoal@126.com
To: pgsql-bugs@postgresql.org
Cc: digoal@126.com
Date: 2017-07-17T03:17:58Z
Lists: pgsql-bugs
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