Re: Query optimization using order by and limit

On 09/21/2011 07:14 PM, Michael Viscuso wrote:
> Check constraints:
> "osmoduleloads_2011_09_14_event_time_check" CHECK (event_time =
> '2011-09-14 00:00:00'::timestamp without time zone)
> "osmoduleloads_2011_09_14_firstloadtime_check" CHECK
> (firstloadtime >= 129604464000000000::bigint::numeric AND
> firstloadtime < 129605328000000000::bigint::numeric)
> Inherits: osmoduleloads

That weird casting can't be helping. I'm not sure if it's your problem
here, but the constraint exclusion code is pretty picky about matching
the thing you're looking for against the CHECK constraint, and this is a
messy one. The bigint conversion in the middle there isn't doing
anything useful for you anyway; you really should simplify this to just
look like this:

firstloadtime >= 129604464000000000::numeric

> SELECT osm_1.*, storefiles_1.*, filepaths_1.*, filepaths_2.* FROM
> (SELECT * FROM osmoduleloads JOIN hosts ON hosts.guid =
> osmoduleloads.host_guid WHERE hosts.guid = '2007075705813916178' AND
> osmoduleloads.firstloadtime >= 129604320000000000 AND
> osmoduleloads.firstloadtime < 129610367990000000 AND hosts.enabled =
> true AND hosts.user_id = 111 ORDER BY osmoduleloads.firstloadtime DESC
> LIMIT 251) AS osm_1 LEFT OUTER JOIN storefiles AS storefiles_1 ON
> osm_1.md5hash = storefiles_1.md5hash LEFT OUTER JOIN filepaths AS
> filepaths_1 ON osm_1.process_filepath_guid = filepaths_1.guid AND
> osm_1.event_time = filepaths_1.event_time LEFT OUTER JOIN filepaths AS
> filepaths_2 ON osm_1.filepath_guid = filepaths_2.guid AND
> osm_1.event_time= filepaths_2.event_time ORDER BY osm_1.firstloadtime
> DESC;
>

What you should start with here is confirming whether or not a simpler
query touches all of the partitions or just the ones you expect it to.
A simpler one like this:

SELECT * FROM osmoduleloads WHERE osmoduleloads.firstloadtime >=
129604320000000000 AND osmoduleloads.firstloadtime < 129610367990000000;

Would be the place to begin. Once you've got that working, then you can
build up more pieces, and see if one of them results in the query not
excluding partitions anymore or not. I can't figure out if you're
running into a basic error here, where constraint exclusion just isn't
working at all, or if you are only having this problem because the query
is too complicated. Figuring that out will narrow the potential solutions.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us