limit clause produces wrong query plan
От | Andrus |
---|---|
Тема | limit clause produces wrong query plan |
Дата | |
Msg-id | ggciha$1v26$1@news.hub.org обсуждение исходный текст |
Список | pgsql-performance |
Adding limit clause causes very slow query: explain analyze select * from firma2.dok where doktyyp='J' order by dokumnr limit 100 "Limit (cost=0.00..4371.71 rows=100 width=1107) (actual time=33189.971..33189.971 rows=0 loops=1)" " -> Index Scan using dok_dokumnr_idx on dok (cost=0.00..278740.01 rows=6376 width=1107) (actual time=33189.959..33189.959 rows=0 loops=1)" " Filter: (doktyyp = 'J'::bpchar)" "Total runtime: 33190.103 ms" Without limit is is fast: explain analyze select * from firma2.dok where doktyyp='J' order by dokumnr "Sort (cost=7061.80..7077.74 rows=6376 width=1107) (actual time=0.119..0.119 rows=0 loops=1)" " Sort Key: dokumnr" " -> Index Scan using dok_doktyyp on dok (cost=0.00..3118.46 rows=6376 width=1107) (actual time=0.101..0.101 rows=0 loops=1)" " Index Cond: (doktyyp = 'J'::bpchar)" "Total runtime: 0.245 ms" How to fix this without dropping dok_doktyyp index so that limit can safely used for paged data access ? indexes: dok_doktyyp: dok(doktyyp) dok_dokumnr_idx: dok(dokumnr) types: dokumnr int primary key doktyyp char(1) Andrus. Using 8.1.4
В списке pgsql-performance по дате отправления: