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 g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
