Re: Changing ORDER BY column slows query dramatically

On 04/12/2013 11:51 AM, brick pglists wrote:

> -> Index Scan Backward using event_20130405_id_pk on
> event_20130405 e (cost=0.00..612732.34 rows=1889715 width=34) (actual
> time=2076.812..2111.274 rows=10100 loops=1)
> Filter: ((tstamp_utc >= '2013-04-05
> 10:00:00'::timestamp without time zone) AND (tstamp_utc <= '2013-04-05
> 18:00:00'::timestamp without time zone) AND (date_utc =
> '2013-04-05'::date) AND (org_id = 216471))

This right here is your culprit. The planner thinks it'll be faster to
grab 100 rows by scanning your primary key backwards and filtering out
the matching utc timestamps and other criteria.

Since it doesn't show up in your GUC list, you should probably increase
your default_statistics_target to 400 or more, analyze, and try again.
The heuristics for the dates aren't complete enough, so it thinks there
are few matches. If that doesn't work and you want a quick, but ugly fix
for this, you can create the following index:

CREATE INDEX event_20130406_id_desc_tstamp_utc_idx
ON event_20130406 (id DESC, tstamp_utc);

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email