Wrong plane for limit after group by

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux