From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | limit clause produces wrong query plan |
Date: | 2008-11-23 20:52:54 |
Message-ID: | ggciha$1v26$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Carey | 2008-11-23 21:30:40 | Re: Hash join on int takes 8..114 seconds |
Previous Message | Tomas Vondra | 2008-11-23 18:47:15 | Re: Hash join on int takes 8..114 seconds |