Re: Bad performance of SELECT ... where id IN (...)

From: Xia Qingran <qingran(dot)xia(at)gmail(dot)com>
To: Omar Kilani <omar(dot)kilani(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Bad performance of SELECT ... where id IN (...)
Date: 2009-10-09 12:31:54
Message-ID: b5ff60410910090531n6864014cl115b0974ed0b6fc4@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Mon, Oct 5, 2009 at 9:58 AM, Omar Kilani <omar(dot)kilani(at)gmail(dot)com> wrote:
> Hi Xia,
>
> Try this patch:
>
> http://treehou.se/~omar/postgresql-8.4.1-array_sel_hack.patch
>
> It's a hack, but it works for us. I think you're probably spending
> most of your query time planning, and this patch helps speed things up
> 10x over here.

Thanks!
I am trying it.

Regards,

Xia Qingran

>
> Regards,
> Omar
>
> On Sun, Sep 27, 2009 at 5:13 PM, Xia Qingran <qingran(dot)xia(at)gmail(dot)com> wrote:
>> On Sat, Sep 26, 2009 at 10:59 PM, Craig James
>> <craig_james(at)emolecules(dot)com> wrote:
>>>
>>> If your user_id is always in a narrow range like this, or even in any range
>>> that is a small fraction of the total, then add a range condition, like
>>> this:
>>>
>>> select * from event where user_id <= 500 and user_id >= 0 and user_id in
>>> (...)
>>>
>>> I did this exact same thing in my application and it worked well.
>>>
>>> Craig
>>>
>>
>> It is a good idea. But In my application, most of the queries' user_id
>> are random and difficult to range.
>> Thanks anyway.
>>
>>
>>
>> --
>> 夏清然
>> Xia Qingran
>> qingran(dot)xia(at)gmail(dot)com
>> Sent from Beijing, 11, China
>> Charles de Gaulle  - "The better I get to know men, the more I find
>> myself loving dogs." -
>> http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>

--
夏清然
Xia Qingran
qingran(dot)xia(at)gmail(dot)com
Sent from Beijing, 11, China
Stephen Leacock - "I detest life-insurance agents: they always argue
that I shall some day die, which is not so." -
http://www.brainyquote.com/quotes/authors/s/stephen_leacock.html

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kenneth Marshall 2009-10-09 13:09:05 Re: Bad performance of SELECT ... where id IN (...)
Previous Message Dave Cramer 2009-10-09 11:57:38 Re: Explain Analyze returns faster than psql or JDBC calls.