Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id CA4CBB5DC24 for ; Wed, 21 Sep 2011 22:48:49 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 08088-08 for ; Thu, 22 Sep 2011 01:48:42 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.186]) by mail.postgresql.org (Postfix) with ESMTP id 83D4DB5DC15 for ; Wed, 21 Sep 2011 22:48:42 -0300 (ADT) Received: from [192.168.1.6] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mrbap1) with ESMTP (Nemesis) id 0LkBS6-1Rcs0u05IQ-00bymr; Thu, 22 Sep 2011 03:48:42 +0200 Message-ID: <4E7A93F9.1050301@2ndQuadrant.com> Date: Wed, 21 Sep 2011 21:48:41 -0400 From: Greg Smith User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.16) Gecko/20110818 Icedove/3.0.11 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Query optimization using order by and limit References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------050301040807040800060107" X-Provags-ID: V02:K0:fuCZC67d5vF/NhQqIWgxNvDB0os2/dgAoyI6MJ/9VJ+ m5JbTuP+qt+kFyYLxxzL+xTjcV7zE0oPDYt8XEhhbOZdO63hsS 5FyR1dO1mf51tHfJrg5NtI/cJlVluTakXEMyyBpfIrhu6w1n0U O2Yq83d5rlqAUvpv6FML4uqCDSSC0LZ7wxa0sOPGZwlSjJ6A9i iATujt4ZzZxDBhSUC5A7e5Vh9UpnOPVhU96x5WFdVbm19vYQQP OyCMBK5Y4QKJiSots+sSoUq7vtMmP3wgT3P+mrvKunvq9rmvFw McyzCBaRsQU3Zvyk9ts2U4AK7PpvjAYWwIOuk3AO4ZUrqyyUqY LfzD8EEZuoaPSPa/NRjk= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201109/340 X-Sequence-Number: 45116 This is a multi-part message in MIME format. --------------050301040807040800060107 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us --------------050301040807040800060107 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
--------------050301040807040800060107--