[PERFORM] Wrong plane for limit after group by
| От | Денис Смирнов |
|---|---|
| Тема | [PERFORM] Wrong plane for limit after group by |
| Дата | |
| Msg-id | CAKEkwroJR2agO+rbpEEJ16=9rgNh8rQHE=gJ23VfoQcDuc7DKg@mail.gmail.com обсуждение исходный текст |
| Список | pgsql-performance |
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 |+--------------------------------------------------------------------------------------------------------------------------------+EXPLAINTime: 0.026squota_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 |+-------------------------------------------------------------------------------------------------------------------------------+EXPLAINTime: 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 |+-----------------------------------------------------------------------------------------------------------------------------------------+EXPLAINTime: 0.026s
В списке pgsql-performance по дате отправления: