Re: Index to help ordering? - Mailing list pgsql-novice

--As of January 17, 2014 4:41:26 PM +0000, James David Smith is alleged to have said:

I have a table of some 100m rows of data. There are 5 columns of data.
When I want to look at this data I typically want to sort it by ppid
(numeric) an then by time (timestamp). This simple select can often
take 15-20 seconds. Would adding an index to these two columns make it
quicker? What is the best way to achieve increased speed for this
common select query?

--As for the rest, it is mine.

An index is likely to make it faster, although I wouldn't quite want to guarantee it.  (I assume you are pulling in all the data?  Otherwise an index on your *conditions* might make sense.  Sorting the data likely takes less time than retrieving all of it.)  Luckily enough it's easy to just try it and check.

Since we are working on that one query in specific, I'd probably create an index on those two columns, in that order: `CREATE INDEX ixd_name ON table (ppid, time)`.  I'm of course assuming that ppid's aren't unique.

But again, if you are retrieving the entire table this may not help - what would help more is to limit the number of records you are retrieving and put an index on that condition.  (Of course, if you are using LIMIT, then the index will definitely help.)

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------