By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 1,094 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

indexing, limit problem

P: 1
Hi all,
I found a big performance difference between two query with different "Limit" and I think it is not make sense

Table: person_item
PKey: (person_id, time)
Index: (time)

Query1) SELECT item FROM person_item WHERE person_id = '3000' ORDER BY time DESC OFFSET 0 LIMIT 10;
total runtime: 0.224 ms

explain result:
Limit (cost=1503.22..1503.25 rows=10 width=8) (actual time=0.140..0.173 rows=10 loops=1)
-> Sort (cost=1503.22..1504.41 rows=475 width=8) (actual time=0.137..0.148 rows=10 loops=1)
Sort Key: "time"
-> Index Scan using person_item_pkey on
person_item (cost=0.00..1482.11 rows=475 width=8) (actual time=0.044..0.093 rows=21 loops=1)
Index Cond: (person_id = 3000)

Query2) SELECT item FROM person_item WHERE person_id = '3000' ORDER BY time DESC OFFSET 0 LIMIT 5;
total runtime: 2092.378 ms

explain result:
Limit (cost=0.00..1229.46 rows=5 width=8) (actual time=2092.321..2092.339 rows=5 loops=1)
-> Index Scan Backward using person_item_index_time
on person_item (cost=0.00..116799.08 rows=475 width=8)(actual time= 2092.316..2092.324 rows=5 loops=1)
Filter: (person_id = 3000)

Why retrieve less records, but the runtime is longer ?
Why postgreSQL use different index to run Query1 and Query2, but they are almost same (just LIMIT is not same)
Dec 31 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.