On Thu, Mar 13, 2014 at 12:26 PM, <fburgess@xxxxxxxxxxxxxxx> wrote: > *** Problem Query *** > > explain (analyze on, buffers on) Select * from measurement this_ > where this_.logdate between '2007-12-19 > 23:38:41.22'::timestamp and '2007-12-20 08:01:04.22'::timestamp > and this_.city_id=25183 order by > this_.logdate asc, this_.peaktemp asc, this_.unitsales asc limit 10000; > [...] > Total runtime: 51717.639 ms <--- *** unacceptable *** Try to create a multi-column index on the partition by (city_id, logdate). Then run the original query and the query without peaktemp and nitsales on the order by. Compare the results, and if the first one will not be satisfying try to add these two columns to the end of the column list of your multi-column index on the order as they appear in your query. It should do the trick. If it wont, please, show the plans. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@xxxxxxxxx -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
