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 <[email protected]> wrote: > From: Anj Adu <[email protected]> > Subject: Re: [PERFORM] sub-select in IN clause results in sequential scan > To: "Angayarkanni" <[email protected]> > Cc: "Grzegorz Jaśkiewicz" <[email protected]>, > [email protected] > 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 <[email protected]>: > > > > 2009/10/29 Grzegorz Jaśkiewicz <[email protected]> > >> > >> > >> On Wed, Oct 28, 2009 at 6:13 PM, Anj Adu <[email protected]> > 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 ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
