limit clause produces wrong query plan

От: Andrus
Тема: limit clause produces wrong query plan
Дата: ,
Msg-id: ggciha$1v26$1@news.hub.org
(см: обсуждение, исходный текст)
Список: pgsql-performance

Скрыть дерево обсуждения

limit clause produces wrong query plan  ("Andrus", )
 Re: limit clause produces wrong query plan  ("Andrus", )
  Re: limit clause produces wrong query plan  ("Scott Marlowe", )
   Re: limit clause produces wrong query plan  ("Andrus", )
    Re: limit clause produces wrong query plan  (Chris, )
    Re: limit clause produces wrong query plan  (PFC, )
   Re: limit clause produces wrong query plan  (Scott Carey, )

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 по дате сообщения:

От: Tomas Vondra
Дата:
Сообщение: Re: Hash join on int takes 8..114 seconds
От: Richard Huxton
Дата:
Сообщение: Re: Increasing pattern index query speed