Re: Query optimization using order by and limit - Mailing list pgsql-performance

Thanks guys,

First of all, I should have included my postgres.conf file with the
original submission.  Sorry about that.  It is now attached.

Based on a recommendation, I also should have shown the parent child
relationship between osmoduleloads and its daily partitioned tables.  to
reduce clutter, It is at the end of this message.

Taking this one step at a time and taking Greg's second suggestion
first, issuing

select * from osmoduleloads WHERE osmoduleloads.firstloadtime >=
129604320000000000 AND osmoduleloads.firstloadtime < 129610367990000000;

appears to only query the appropriate daily tables (2011_09_13 through
2011_09_20 - http://explain.depesz.com/s/QCG).  So it appears that
constraint_exclusion is working properly.  Putting a limit on the query
like:

select * from osmoduleloads WHERE osmoduleloads.firstloadtime >=
129604320000000000 AND osmoduleloads.firstloadtime < 129610367990000000
limit 251;

has the result that I'd expect to see http://explain.depesz.com/s/O7fZ.
Ordering by firstloadtime AND limiting like:

select * from osmoduleloads WHERE osmoduleloads.firstloadtime >=
129604320000000000 AND osmoduleloads.firstloadtime < 129610367990000000
order by firstloadtime desc limit 251;

also has the result that I'd expect to see
http://explain.depesz.com/s/RDh.

Adding the hosts join condition to the mix was still OK
http://explain.depesz.com/s/2Ns.

Adding the hosts.enabled condition was still OK
http://explain.depesz.com/s/UYN.

Adding the hosts.user_id = 111 started the descent but it appears to
still be obeying the proper contraint_exclusion that I'd expect, just
with a ton of rows returned from the most recent daily tables
http://explain.depesz.com/s/4WE.

Adding the final condition hosts_guid = '2007075705813916178' is what
ultimately kills it http://explain.depesz.com/s/8zy.  By adding the
host_guid, it spends considerably more time in the older tables than
without this condition and I'm not sure why.

Thanks Greg for the recommendation to step through it like that -
hopefully this helps get us closer to a resolution.

Greg/Tom, you are correct, these columns should be modified to whatever
is easiest for Postgres to recognize 64-bit unsigned integers.  Would
you still recommend bigint for unsigned integers?  I likely read the
wrong documentation that suggested bigint for signed 64-bit integers and
numeric(20) for unsigned 64-bit integers.

Thanks again for all your help!  Perhaps 15 hours of pouring over
explain logs will finally pan out!

Mike

cb=# \d+ osmoduleloads;
                                     Table "public.osmoduleloads"
        Column         |            Type             |
Modifiers       | Storage  | Description
-----------------------+-----------------------------+-----------------------+----------+-------------
 guid                  | numeric(20,0)               | not
null              | main     |
 osprocess_guid        | numeric(20,0)               | not
null              | main     |
 filepath_guid         | numeric(20,0)               | not
null              | main     |
 firstloadtime         | numeric(20,0)               | not
null              | main     |
 md5hash               | bytea                       | not
null              | extended |
 host_guid             | numeric(20,0)               | default
NULL::numeric | main     |
 process_create_time   | numeric(20,0)               | default
NULL::numeric | main     |
 process_filepath_guid | numeric(20,0)               | default
NULL::numeric | main     |
 event_time            | timestamp without time zone
|                       | plain    |
Indexes:
    "osmoduleloads_pkey" PRIMARY KEY, btree (guid)
Child tables: osmoduleloads_2001_12_31,
              osmoduleloads_2010_10_11,
              osmoduleloads_2010_10_12,
              osmoduleloads_2010_10_13,
              osmoduleloads_2011_07_27,
              osmoduleloads_2011_08_04,
              osmoduleloads_2011_08_05,
              osmoduleloads_2011_08_06,
              osmoduleloads_2011_08_07,
              osmoduleloads_2011_08_08,
              osmoduleloads_2011_08_09,
              osmoduleloads_2011_08_10,
              osmoduleloads_2011_08_11,
              osmoduleloads_2011_08_12,
              osmoduleloads_2011_08_13,
              osmoduleloads_2011_08_14,
              osmoduleloads_2011_08_15,
              osmoduleloads_2011_08_16,
              osmoduleloads_2011_08_17,
              osmoduleloads_2011_08_18,
              osmoduleloads_2011_08_19,
              osmoduleloads_2011_08_20,
              osmoduleloads_2011_08_21,
              osmoduleloads_2011_08_22,
              osmoduleloads_2011_08_23,
              osmoduleloads_2011_08_24,
              osmoduleloads_2011_08_25,
              osmoduleloads_2011_08_26,
              osmoduleloads_2011_08_27,
              osmoduleloads_2011_08_28,
              osmoduleloads_2011_08_29,
              osmoduleloads_2011_08_30,
              osmoduleloads_2011_08_31,
              osmoduleloads_2011_09_01,
              osmoduleloads_2011_09_02,
              osmoduleloads_2011_09_03,
              osmoduleloads_2011_09_04,
              osmoduleloads_2011_09_05,
              osmoduleloads_2011_09_06,
              osmoduleloads_2011_09_07,
              osmoduleloads_2011_09_08,
              osmoduleloads_2011_09_09,
              osmoduleloads_2011_09_10,
              osmoduleloads_2011_09_11,
              osmoduleloads_2011_09_12,
              osmoduleloads_2011_09_13,
              osmoduleloads_2011_09_14,
              osmoduleloads_2011_09_15,
              osmoduleloads_2011_09_16,
              osmoduleloads_2011_09_17,
              osmoduleloads_2011_09_18,
              osmoduleloads_2011_09_19,
              osmoduleloads_2011_09_20,
              osmoduleloads_2011_12_01
Has OIDs: no

On 9/21/2011 10:09 PM, Tom Lane wrote:
> Greg Smith <greg@2ndQuadrant.com> writes:
>> 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
> I have a more aggressive suggestion: change all the numeric(20,0) fields
> to bigint.  Unless the OP actually needs values wider than 64 bits,
> the choice to use numeric is a significant performance penalty for
> nothing.
>
>             regards, tom lane
>