Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: Teja Jakkidi <teja.jakkidi05@gmail.com>
Mime-Version: 1.0 (1.0)
Subject: Re: Performance issue after creating partitions
Date: Fri, 26 Aug 2022 11:46:12 -0700
Message-Id: <738BC8F9-5F1E-4E8F-8EB9-DEE6EDAA740B@gmail.com>
References: 
 <01000182d27cd99e-5a38a3c8-7060-4969-a24b-cf879b9f5bf7-000000@email.amazonses.com>
Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
In-Reply-To: 
 <01000182d27cd99e-5a38a3c8-7060-4969-a24b-cf879b9f5bf7-000000@email.amazonses.com>
To: Doug Reynolds <mav@wastegate.net>, gogala.mladen@gmail.com
Archived-At: 
 <https://www.postgresql.org/message-id/738BC8F9-5F1E-4E8F-8EB9-DEE6EDAA740B%40gmail.com>
Precedence: bulk

Thank you, Doug and Malden for your inputs.

As suggested by Doug I tried replacing the trunc() with below command:
=E2=80=9C order_date >=3D to_date(..) and order_date < to_date(..) + interva=
l '1' day=E2=80=9D

What happens here is: My query has 2tables : A and B, both are having an inn=
er join on company code and order date. We have A.Company_code, A.order_date=
 filter in the where condition which was having trun() previously but now I r=
eplaced it with the command suggested by Doug. The company_code filter is wo=
rking for both tables A and B, the query is searching only the partitions pa=
rticular to that country code. However for order_date condition, the partiti=
ons for A table are being utilized correctly I.e the query is searching dire=
ctly in only one partition based on that date. But for table B, it is still s=
earching all the partitions, it is not applying the where A.order_date condi=
tion for B tables partitions while searching.
I do not understand why the A.order_date filter is not being applied to tabl=
e B where as A.company_code is being applied to both tables.

Thanks and Regards,
J. Teja Sri.

> On Aug 24, 2022, at 5:52 PM, Doug Reynolds <mav@wastegate.net> wrote:
>=20
> =EF=BB=BFYou need to change your query to not use the trunc.  The TRUNC st=
atement forces it to trunc every row in the table. =20
>=20
> If you do order_date >=3D to_date(..) and order_date < to_date(..) + inter=
val '1' day, it will use the partition range to narrow down to the correct p=
artition first.
>=20
> Sent from my iPhone
>=20
>> On Aug 24, 2022, at 6:41 PM, Teja Jakkidi <teja.jakkidi05@gmail.com> wrot=
e:
>>=20
>> =EF=BB=BFHello Admin team,
>>=20
>> We have a Postgres 13 server set up on Google Cloud. Recently we created p=
artitions as below for the tables:
>> Range on =E2=80=98order_date=E2=80=99  column
>>        List on =E2=80=98country_code=E2=80=99 column
>>=20
>> Quarterly partitions for range.
>>=20
>> Order date column is a time stamp column and when we are using this colum=
n as a filter in where condition : trunc(order_date) =3D to_date(=E2=80=9920=
22-04-01=E2=80=99,=E2=80=99YYYY-MM-DD=E2=80=99), the query scans all the par=
titions dated back from the year 2000. So, instead of going to the 2022 Q2 p=
artition directly, the query is scanning all the partitions.
>> But when we remove the trunc() function and just specify order_date =3D=E2=
=80=982022-04-01 07:02:30=E2=80=99, this works as expected. It goes to the c=
orrect partition directly and gets the data.=20
>> Can Anyone help me on what=E2=80=99s happening in the first case and why a=
ll the partitions are being scanned?
>> For the first case, when there is a non partition table, it is behaving w=
ay better.
>>=20
>> Thanks,
>> J. Teja.
>>=20
>=20