Message-ID: <51686787.3050308@optionshouse.com>
Date: Fri, 12 Apr 2013 14:59:03 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130308 Thunderbird/17.0.4
MIME-Version: 1.0
To: brick pglists <brickpglists@gmail.com>
CC: <pgsql-performance@postgresql.org>
Subject: Re: Changing ORDER BY column slows query dramatically
References: 
 <CALXym+Jz4MRF0ZMoSqE4+xEWqjfKnc8LTawaRxw9QEsSxb2y2Q@mail.gmail.com>
In-Reply-To: 
 <CALXym+Jz4MRF0ZMoSqE4+xEWqjfKnc8LTawaRxw9QEsSxb2y2Q@mail.gmail.com>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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@optionshouse.com

______________________________________________

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance