Re: Query optimization using order by and limit

Thanks Ken,

I'm discussing with my coworker how to best make that change *as we
speak*. Do you think this will also resolve the original issue I'm
seeing where the query doesn't "limit out properly" and spends time in
child tables that won't yield any results? I was hoping that by using
the check constraints, I could query over a week or month's worth of
partitioned tables and the combination of order by and limit would
eliminate any time searching unnecessary tables but that doesn't appear
to be true. (I'm still very new to high-end Postgres performance so I
could be mistaken.)

Regardless, in the meantime, I'll switch those columns to bigint instead
of numeric and have an update as soon as possible.

Thanks for your help!

Mike

On 9/22/2011 9:41 AM, ktm(at)rice(dot)edu wrote:
> On Wed, Sep 21, 2011 at 11:22:53PM -0400, Tom Lane wrote:
>> Michael Viscuso <michael(dot)viscuso(at)getcarbonblack(dot)com> writes:
>>> 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.
>> Unsigned? Oh, hm, that's a bit of a problem because we don't have any
>> unsigned types. If you really need to go to 2^64 and not 2^63 then
>> you're stuck with numeric ... but that last bit is costing ya a lot.
>>
>> regards, tom lane
>>
> Hi Michael,
>
> If you have access to the application, you can map the unsigned 64-bits
> to the PostgreSQL signed 64-bit type with a simple subtraction. That will
> allow you to drop all the numeric use. Also if the guid is a 64-bit
> values stuffed into a numeric(20), you can do it there as well. I achieved
> a hefty performance boost by making those application level changes in a
> similar situation.
>
> Regards,
> Ken