Re: Index to help ordering?
От | James David Smith |
---|---|
Тема | Re: Index to help ordering? |
Дата | |
Msg-id | CAMu32ADRfBB_sPqf1SRm7FyPCqjhEYgqqTdRO26bUKuybMQamA@mail.gmail.com обсуждение исходный текст |
Ответ на | Index to help ordering? (James David Smith <james.david.smith@gmail.com>) |
Ответы |
Re: Index to help ordering?
|
Список | pgsql-novice |
On 17 January 2014 20:47, Daniel Staal <DStaal@usa.net> wrote: > --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.) Hi Daniel, ppid is not unique. I am often not pulling in all of the day. Far from it. 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? Thanks for your help, James
В списке pgsql-novice по дате отправления: