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

Try replacing the 'current_timestamp - interval 8 days' portion with explicit values (e.g. partitioned_column < '2009-10-21'::date ) and see if that works. I think the query planner can only use explicit values to determine if it should go straight to partitioned tables.

Bob

--- On Thu, 10/29/09, Anj Adu <fotographs(at)gmail(dot)com> wrote:

> From: Anj Adu <fotographs(at)gmail(dot)com>
> Subject: Re: [PERFORM] sub-select in IN clause results in sequential scan
> To: "Angayarkanni" <kangayarkanni(at)gmail(dot)com>
> Cc: "Grzegorz Jaśkiewicz" <gryzman(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
> Date: Thursday, October 29, 2009, 10:10 AM
> 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
> >
>
> --
> 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
>