Re: Index to help ordering?

--As of January 20, 2014 10:35:41 AM +0000, James David Smith is alleged to 
have said:

> Basically I have about 75,000 people (ppid) and a record for each
> minute of the day for each person. So 24 hours x 60 minutes x 75,000
> people.
>
> I 'normally' want to just view one person's 'day' at a time. So I
> might do something like this:
>
> SELECT ppid, point_time, mode, concentration FROM table WHERE ppid =
> '43' ORDER BY point_time;
>
> Does this revise how you think I should go about speeding up this query?

--As for the rest, it is mine.

Quite a bit.  ;)  First off: If you only want to view one day at a time, 
I'd put that into the WHERE clause.  This could be done several ways: 
BETWEEN start and end time of the day, or you could reformat to exclude the 
hours/minutes, etc.  (If it's only the most recent day, easiest is probably 
to ask for anything newer than midnight last night - or whenever your day 
begins.)

Secondly, I'd probably advise two indexes: One on ppid, and one on however 
you decide to select the time.  (Note that you can create an index on a 
function: if you wanted to use 'date_trunc' to get the day, you could 
create an index on `date_trunc('day', point_time)`, and a WHERE clause 
would use that index instead of computing it for each item at query time.)

Once I'd done that (and run EXPLAIN, etc. to see how things are going.), if 
things were still too slow I might think about other options.  You might 
think about partitioning the table on either the ppid or the point_time, 
depending on how easy it is to set up and how good you are at automating 
the partitioning process.  (You have to create a sub-table for each 
partition, and how you divide things best would take some fiddling.)

But I think if you put the time into the query and create those two 
indexes, you'll find things are likely fast enough.

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.
---------------------------------------------------------------