Tom is spot on with his suggestions. The only thing that I'll add is that sometimes there is a mismatch with timestamp resolution, which prevents a direct inner join. In this case, you filter both partitioned tables with the greater than/less than constants, THEN complete the inner join with truncated dates. You will still have to do a full scan; however, you'll only be doing a full scan of the selected partitions. Other than that, you need to provide the query for additional advice. Doug > On Aug 30, 2022, at 4:40 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > Teja Jakkidi <teja.jakkidi05@xxxxxxxxx> writes: >> Anyone ever encountered the below situation as what I am noticing with partitions? > > You haven't shown us your query, so any answer would be blind speculation. > > However, in the spirit of blind speculation, I'm wondering if you're > expecting those range constraints to propagate across a join. They > won't; you'd need to duplicate the conditions for the other table. > > That is, if you have WHERE+JOIN/ON conditions amounting to > > WHERE a.a = b.b AND b.b = constant > > the planner is able to derive "a.a = constant" on the assumption of > transitivity, and use that to constrain the scan of table a (ie, > use an index, reject partitions at plan time, etc). But no such > deduction happens for > > WHERE a.a = b.b AND b.b >= constant > > If you want a constrained scan of a, you need to write it out: > > WHERE a.a = b.b AND b.b >= constant AND a.a >= constant > > regards, tom lane > >
