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
shouldgo straight to partitioned tables.
Bob
--- On Thu, 10/29/09, Anj Adu <fotographs@gmail.com> wrote:
> From: Anj Adu <fotographs@gmail.com>
> Subject: Re: [PERFORM] sub-select in IN clause results in sequential scan
> To: "Angayarkanni" <kangayarkanni@gmail.com>
> Cc: "Grzegorz Jaśkiewicz" <gryzman@gmail.com>, pgsql-performance@postgresql.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@gmail.com>:
> >
> > 2009/10/29 Grzegorz Jaśkiewicz <gryzman@gmail.com>
> >>
> >>
> >> On Wed, Oct 28, 2009 at 6:13 PM, Anj Adu <fotographs@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>