Re: [BUGS] Very slow query in PostgreSQL 9.3.3

On Thu, Mar 13, 2014 at 12:26 PM, <fburgess(at)radiantblue(dot)com> 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(dot)ru(at)gmail(dot)com