From: Shaun Thomas <sthomas@optionshouse.com>
To: brick pglists <brickpglists@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Changing ORDER BY column slows query dramatically
Date: Fri, 12 Apr 2013 14:59:03 -0500
Message-ID: <51686787.3050308@optionshouse.com> (raw)
In-Reply-To: <CALXym+Jz4MRF0ZMoSqE4+xEWqjfKnc8LTawaRxw9QEsSxb2y2Q@mail.gmail.com>
References: <CALXym+Jz4MRF0ZMoSqE4+xEWqjfKnc8LTawaRxw9QEsSxb2y2Q@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@optionshouse.com, brickpglists@gmail.com
Subject: Re: Changing ORDER BY column slows query dramatically
In-Reply-To: <51686787.3050308@optionshouse.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox