[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] slow query performance
From:       Mark Kirkwood <markir () paradise ! net ! nz>
Date:       2003-10-31 21:18:14
[Download RAW message or body]


Dave Weaver wrote:

>>- clustering the "obs" table on "station"
>>    
>>
>
>Sorry, I don't understand what you mean by this - can you explain?
>
>  
>
Supposing obs_pkey is on (station, valid_time):

cluster obs_pkey on obs

will re-order the rows in obs based on the index obs_pkey. (This is 
clustering on 'station' and 'valid_time', to do just station you could 
use an index on just 'station').

The down side is that the row ordering is slowly lost as rows are 
updated, so periodic running of the cluster command is needed - this is 
a pain as it will take a while for 13 million row table.

regards

Mark


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
[prev in list] [next in list] [prev in thread] [next in thread]