Hi,
I've faced a problem that a query without limit works much faster than with
one. Steps to reproduce
create extension pg_trgm;
create table t (id serial, val text, constraint t_pk primary key (id));
insert into t (val) select (random() * 100)::text from generate_series(1,
1000000);
create index t_val_idx on t using gin (val gin_trgm_ops);
quota_patient> explain (analyze,buffers) select id from t where val like (
select '6'::text) group by id;
+--------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN
|
|--------------------------------------------------------------------------------------------------------------------------------|
| HashAggregate (cost=6401.14..6451.14 rows=5000 width=4) (actual
time=22.861..22.861 rows=0 loops=1) |
| Group Key: id
|
| Buffers: shared hit=5158
|
| InitPlan 1 (returns $0)
|
| -> Result (cost=0.00..0.01 rows=1 width=32) (actual
time=0.002..0.002 rows=1 loops=1) |
| -> Bitmap Heap Scan on t (cost=50.75..6388.63 rows=5000 width=4)
(actual time=22.835..22.835 rows=0 loops=1) |
| Recheck Cond: (val ~~ $0)
|
| Rows Removed by Index Recheck: 10112
|
| Heap Blocks: exact=5097
|
| Buffers: shared hit=5158
|
| -> Bitmap Index Scan on t_val_idx (cost=0.00..49.50 rows=5000
width=0) (actual time=8.762..8.762 rows=10112 loops=1) |
| Index Cond: (val ~~ $0)
|
| Buffers: shared hit=61
|
| Planning time: 0.166 ms
|
| Execution time: 22.970 ms
|
+--------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN
Time: 0.026s
quota_patient> explain (analyze,buffers) select id from t where val like (
select '6'::text) group by id limit 1;
+-------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN
|
|-------------------------------------------------------------------------------------------------------------------------------|
| Limit (cost=0.43..7.41 rows=1 width=4) (actual time=439.561..439.561
rows=0 loops=1) |
| Buffers: shared hit=9105
|
| InitPlan 1 (returns $0)
|
| -> Result (cost=0.00..0.01 rows=1 width=32) (actual
time=0.002..0.002 rows=1 loops=1) |
| -> Group (cost=0.42..34865.93 rows=5000 width=4) (actual
time=439.560..439.560 rows=0 loops=1) |
| Group Key: id
|
| Buffers: shared hit=9105
|
| -> Index Scan using t_pk on t (cost=0.42..34853.43 rows=5000
width=4) (actual time=439.557..439.557 rows=0 loops=1) |
| Filter: (val ~~ $0)
|
| Rows Removed by Filter: 1000000
|
| Buffers: shared hit=9105
|
| Planning time: 0.205 ms
|
| Execution time: 439.610 ms
|
+-------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN
Time: 0.443s
I can't understand why adding limit after group by makes a planner fall to
non optimal plan. I tried to add more work_mem (up to 100Mb) but no effect.
Is it a planner bug?
BTW if I don't use subquery after like everything is ok
quota_patient> explain (analyze,buffers) select id from t where val like '6'
::text group by id limit 1;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN
|
|-----------------------------------------------------------------------------------------------------------------------------------------|
| Limit (cost=24.03..24.04 rows=1 width=4) (actual time=23.048..23.048
rows=0 loops=1) |
| Buffers: shared hit=5158
|
| -> Group (cost=24.03..24.04 rows=1 width=4) (actual
time=23.046..23.046 rows=0 loops=1)
|
| Group Key: id
|
| Buffers: shared hit=5158
|
| -> Sort (cost=24.03..24.04 rows=1 width=4) (actual
time=23.046..23.046 rows=0 loops=1)
|
| Sort Key: id
|
| Sort Method: quicksort Memory: 25kB
|
| Buffers: shared hit=5158
|
| -> Bitmap Heap Scan on t (cost=20.01..24.02 rows=1
width=4) (actual time=23.036..23.036 rows=0 loops=1) |
| Recheck Cond: (val ~~ '6'::text)
|
| Rows Removed by Index Recheck: 10112
|
| Heap Blocks: exact=5097
|
| Buffers: shared hit=5158
|
| -> Bitmap Index Scan on t_val_idx (cost=0.00..20.01
rows=1 width=0) (actual time=8.740..8.740 rows=10112 loops=1) |
| Index Cond: (val ~~ '6'::text)
|
| Buffers: shared hit=61
|
| Planning time: 0.190 ms
|
| Execution time: 23.105 ms
|
+-----------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN
Time: 0.026s
