[BUGS] BUG #14746: sub query's plan not right?

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.09rows=99500 width=44)  Filter: (((SubPlan 4)).aid IS NOT NULL)  SubPlan 4    ->  Limit
(cost=0.57..1.89rows=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
limit100001                       
 
) t 
where (t.b).aid is not null;                                          QUERY PLAN

 
------------------------------------------------------------------------------------------------Subquery Scan on t
(cost=0.00..191854.32rows=99500 width=44)  Filter: ((t.b).aid IS NOT NULL)  ->  Limit  (cost=0.00..190854.32
rows=100000width=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
limit100001                       
 
) t 
where (t.b).aid is not null;                                                                 QUERY PLAN
                                               
 

-----------------------------------------------------------------------------------------------------------------------------------------------Subquery
Scanon 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=1width=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)Planningtime: 0.145 msExecution 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
Scanon 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.89rows=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.238msExecution time: 2294.653 ms
 
(20 rows)
```

is this a bug ? or it can be improved?

thank you, best regards, 
digoal


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs