473,388 Members | 1,408 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

indexing, limit problem

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
0 2520

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

Similar topics

21
by: Hilde Roth | last post by:
This may have been asked before but I can't find it. If I have a rectangular list of lists, say, l = ,,], is there a handy syntax for retrieving the ith item of every sublist? I know about for i...
6
by: Michael Drumheller | last post by:
(If you're not interested in NumArray, please skip this message.) I am new to NumArray and I wonder if someone can help me with array-indexing. Here's the basic situation: Given a rank-2 array...
1
by: Chuck Chekuri | last post by:
Hi, We have a 3TB oracle db. 24x7 operation with data constantly coming in to the DB. Users from all over the world use a web based applicaton to query and work on the data. Out transaction...
1
by: Perre Van Wilrijk | last post by:
Hi, I've got a full text index which works fine, SQLSERVER2000/WIN 2000 SERVER. The system requires to update indexes immediately, so I use a timestamp field to enable this. No problems so...
108
by: Bryan Olson | last post by:
The Python slice type has one method 'indices', and reportedly: This method takes a single integer argument /length/ and computes information about the extended slice that the slice object would...
9
by: John Sidney-Woollett | last post by:
Hi I'm building a web app where changes to customer orders are logged in the following table, and I worried about the time that it will take to locate records that need further...
10
by: Lyle Fairfield | last post by:
By default mdb etc files are not searched by Indexing Service. The default can be changed in Windows XP Pro using Group Policy. I can find no suggested way to make the change in the Windows XO...
7
by: Ryan | last post by:
I have a bit of a problem with regards an indexing strategy. Well, basically there is no indexing strategy on a set of data I have at work. Now, I didn't create the design as I would have allowed...
2
by: =?Utf-8?B?SmVycnkgQw==?= | last post by:
I have a server 2008 IIS 7.0 with indexing service installed. I have created the catalog and have a test page using these posts:...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.