Re: [BUGS] Very slow query in PostgreSQL 9.3.3

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux