From: | "Jan Harders" <jan(at)delinquent(dot)de> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | increasing LIMIT with ORDER BY changes queryplan (7.4) |
Date: | 2006-10-18 13:35:02 |
Message-ID: | 001801c6f2ba$377aee80$6e15a8c0@jan0 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi everyone,
I'm new to postgre so please don't take anything implied but rather throw
questions when something's unclear.
Here's my problem or rather my question: I have a table with a category
(four different values here), some data-fields I need and a few
timestampfields indicating when which datafield was last updated. I've set
up Indexes on the timestampfields with HAVING category = 'foo' since I found
that to give me a serious performance boost (I usually query just one
category at a time).
Table has about 9 million rows.
I'm querying with
SELECT "id", "title", "subtitle", "submissions" FROM "mytable" WHERE
"category" = 'foo' ORDER BY "last_update_submissions" ASC LIMIT 50
and it's working fine, speed is ok.
EXPLAIN says:
---snip---
Limit (cost=0.00..2248.88 rows=50 width=434)
-> Index Scan using index__last_update_submissions__category__foo on
mytable (cost=0.00..531590.39 rows=11819 width=434)
Filter: ((category)::text = 'foo'::text)
---snip---
where index__last_update_submissions__category__foo is my special index.
When I increase LIMIT though, it get's strange.
with LIMIT 1899 it's still the same queryplan, on LIMIT 1900 it changes to:
---snip---
Limit (cost=85409.87..85414.62 rows=1900 width=434)
-> Sort (cost=85409.87..85439.42 rows=11819 width=434)
Sort Key: last_update_submissions
-> Index Scan using index__last_update_submissions__category__foo
on mytable (cost=0.00..82989.63 rows=11819 width=434)
Index Cond: ((category)::text = 'foo'::text)
---snip---
and, as guessed, takes forever. tried to ANALYZE the table but no change.
Anyone got any ideas? I just don't understand why it's sorting the values
while the index should already be sorted...
Oh, btw, I'm on 7.4 (sarge stable version). Could this behaviour change if I
just upgrade to 8.1?
any input is appreciated - it's not a critical project but just a private
one I'm using to get familiar with postgre.
greets from hamburg, germany,
jan harders
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2006-10-18 13:42:29 | Re: increasing LIMIT with ORDER BY changes queryplan (7.4) |
Previous Message | Shane Ambler | 2006-10-18 13:18:31 | Re: COPY FROM STDIN instead of INSERT |