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