Skip site navigation (1) Skip section navigation (2)

Re: sub-select in IN clause results in sequential scan

From: Anj Adu <fotographs(at)gmail(dot)com>
To: Angayarkanni <kangayarkanni(at)gmail(dot)com>
Cc: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: sub-select in IN clause results in sequential scan
Date: 2009-10-29 14:10:24
Message-ID: f2fd819a0910290710i324b153fxeb0b01ceb7831083@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Join did not help. A sequential scan is still being done. The
hardcoded value in the IN clause performs the best. The time
difference is more than an order of magnitude.

2009/10/29 Angayarkanni <kangayarkanni(at)gmail(dot)com>:
>
> 2009/10/29 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
>>
>>
>> On Wed, Oct 28, 2009 at 6:13 PM, Anj Adu <fotographs(at)gmail(dot)com> wrote:
>>>
>>> Postgres consistently does a sequential scan on the child partitions
>>> for this query
>>>
>>> select * from partitioned_table
>>> where partitioned_column > current_timestamp - interval 8 days
>>> where x in (select yy from z where colname like 'aaa%')
>>>
>>> If I replace the query with
>>>
>>> select * from partitioned_table
>>> where partitioned_column > current_timestamp - interval 8 days
>>> where x in (hardcode_value)
>>>
>>> The results are in line with expectation (very fast and uses a Bitmap
>>> Index Scan on the column X)
>>> \
>>
>> use JOIN luke..
>>
>> --
>> GJ
>
> Yes you try by using Join
>
> JAK
>

In response to

Responses

pgsql-performance by date

Next:From: Michal J. KubskiDate: 2009-10-29 14:28:07
Subject: Re: query planning different in plpgsql?
Previous:From: AngayarkanniDate: 2009-10-29 10:32:38
Subject: Re: sub-select in IN clause results in sequential scan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group