Skip site navigation (1) Skip section navigation (2)

limit clause produces wrong query plan

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 (view raw or flat)
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


Responses

pgsql-performance by date

Next:From: Scott CareyDate: 2008-11-23 21:30:40
Subject: Re: Hash join on int takes 8..114 seconds
Previous:From: Tomas VondraDate: 2008-11-23 18:47:15
Subject: Re: Hash join on int takes 8..114 seconds

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group