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@xxxxxxxxxxxxxxxx
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
