[BUGS] BUG #14685: use ctid filter tuples will generate LOOP, very very slow - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      14685
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 10beta1
Operating system:   CentOS 6.x x64
Description:

test case

```
create table test1(c1 int, c2 int);  
insert into test1 select random()*1000, random()*1000 from
generate_series(1,10000); 
```

when use ctid not in , there is LOOP with Materialize.

```
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from
test1 where ctid not in (select max(ctid) from test1 group by c1,c2);
            QUERY 
PLAN

---------------------------------------------------------------------------------------------------------------------------------------------Seq
Scanon public.test1  (cost=222.97..90895.39 rows=5085 width=8) (actual 
time=54.451..7741.146 rows=51 loops=1)  Output: test1.c1, test1.c2  Filter: (NOT (SubPlan 1))  Rows Removed by Filter:
9949 Buffers: shared hit=90  SubPlan 1
 
    发生了LOOP
    ->  Materialize  (cost=222.97..238.23 rows=1017 width=14) (actual
time=0.001..0.297 rows=5000 loops=10000)          Output: (max(test1_1.ctid)), test1_1.c1, test1_1.c2          Buffers:
sharedhit=45          ->  HashAggregate  (cost=222.97..233.14 rows=1017 width=14) 
(actual time=4.757..6.655 rows=9949 loops=1)                Output: max(test1_1.ctid), test1_1.c1, test1_1.c2
    Group Key: test1_1.c1, test1_1.c2                Buffers: shared hit=45                ->  Seq Scan on public.test1
test1_1 (cost=0.00..146.70 
rows=10170 width=14) (actual time=0.005..1.588 rows=10000 loops=1)                      Output: test1_1.c1, test1_1.c2,
test1_1.ctid                     Buffers: shared hit=45Planning time: 0.121 msExecution time: 7741.277 ms
 
(18 rows)
```

when i use another user defined column , there is no LOOP.

test case

```
drop table test1;
create table test1(id int, c1 int, c2 int);  
insert into test1 select id, random()*1000, random()*1000 from
generate_series(1,10000) t(id); 

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from
test1 where id not in (select max(id) from test1 group by c1,c2);
        QUERY PLAN                                                                
 

---------------------------------------------------------------------------------------------------------------------------------------------Seq
Scanon public.test1  (cost=1048.18..1243.43 rows=5610 width=12) 
(actual time=11.762..13.627 rows=48 loops=1)  Output: test1.id, test1.c1, test1.c2  Filter: (NOT (hashed SubPlan 1))
RowsRemoved by Filter: 9952  Buffers: shared hit=110  SubPlan 1    ->  GroupAggregate  (cost=921.96..1045.38 rows=1122
width=12)(actual 
time=5.355..9.162 rows=9952 loops=1)          Output: max(test1_1.id), test1_1.c1, test1_1.c2          Group Key:
test1_1.c1,test1_1.c2          Buffers: shared hit=55          ->  Sort  (cost=921.96..950.01 rows=11220 width=12)
(actual
time=5.350..6.101 rows=10000 loops=1)                Output: test1_1.c1, test1_1.c2, test1_1.id                Sort
Key:test1_1.c1, test1_1.c2                Sort Method: quicksort  Memory: 853kB                Buffers: shared hit=55
            ->  Seq Scan on public.test1 test1_1  (cost=0.00..167.20 
rows=11220 width=12) (actual time=0.004..1.528 rows=10000 loops=1)                      Output: test1_1.c1, test1_1.c2,
test1_1.id                     Buffers: shared hit=55Planning time: 58.784 msExecution time: 13.685 ms
 
(20 rows)
```


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