From: Tom Lane <tgl@sss.pgh.pa.us>
To: Teja Jakkidi <teja.jakkidi05@gmail.com>
cc: Doug Reynolds <mav@wastegate.net>, gogala.mladen@gmail.com,
        pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Performance issue after creating partitions
In-reply-to: <6F2BE85B-F6D0-4D4C-9DEA-EEE09D97B8EF@gmail.com>
References: <738BC8F9-5F1E-4E8F-8EB9-DEE6EDAA740B@gmail.com>
 <6F2BE85B-F6D0-4D4C-9DEA-EEE09D97B8EF@gmail.com>
Comments: In-reply-to Teja Jakkidi <teja.jakkidi05@gmail.com>
	message dated "Tue, 30 Aug 2022 14:21:50 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3757069.1661895637.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 30 Aug 2022 17:40:37 -0400
Message-ID: <3757070.1661895637@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3757070.1661895637%40sss.pgh.pa.us>
Precedence: bulk

Teja Jakkidi <teja.jakkidi05@gmail.com> writes:
> Anyone ever encountered the below situation as what I am noticing with p=
artitions?

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 =3D b.b AND b.b =3D constant

the planner is able to derive "a.a =3D 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 =3D b.b AND b.b >=3D constant

If you want a constrained scan of a, you need to write it out:

	WHERE a.a =3D b.b AND b.b >=3D constant AND a.a >=3D constant

			regards, tom lane