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: Tue, 30 Aug 2022 14:21:50 -0700
Message-Id: <6F2BE85B-F6D0-4D4C-9DEA-EEE09D97B8EF@gmail.com>
References: <738BC8F9-5F1E-4E8F-8EB9-DEE6EDAA740B@gmail.com>
Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
In-Reply-To: <738BC8F9-5F1E-4E8F-8EB9-DEE6EDAA740B@gmail.com>
To: Doug Reynolds <mav@wastegate.net>, gogala.mladen@gmail.com
Archived-At: 
 <https://www.postgresql.org/message-id/6F2BE85B-F6D0-4D4C-9DEA-EEE09D97B8EF%40gmail.com>
Precedence: bulk

Anyone ever encountered the below situation as what I am noticing with parti=
tions?

Thanks,
Teja. J.

> On Aug 26, 2022, at 11:46 AM, Teja Jakkidi <teja.jakkidi05@gmail.com> wrot=
e:
>=20
> =EF=BB=BFThank you, Doug and Malden for your inputs.
>=20
> 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(..) + inter=
val '1' day=E2=80=9D
>=20
> What happens here is: My query has 2tables : A and B, both are having an i=
nner join on company code and order date. We have A.Company_code, A.order_da=
te filter in the where condition which was having trun() previously but now I=
 replaced it with the command suggested by Doug. The company_code filter is w=
orking for both tables A and B, the query is searching only the partitions p=
articular to that country code. However for order_date condition, the partit=
ions for A table are being utilized correctly I.e the query is searching dir=
ectly in only one partition based on that date. But for table B, it is still=
 searching all the partitions, it is not applying the where A.order_date con=
dition for B tables partitions while searching.
> I do not understand why the A.order_date filter is not being applied to ta=
ble B where as A.company_code is being applied to both tables.
>=20
> Thanks and Regards,
> J. Teja Sri.
>=20
>> 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 s=
tatement forces it to trunc every row in the table. =20
>>=20
>> If you do order_date >=3D to_date(..) and order_date < to_date(..) + inte=
rval '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> wr=
ote:
>>>=20
>>> =EF=BB=BFHello Admin team,
>>>=20
>>> We have a Postgres 13 server set up on Google Cloud. Recently we created=
 partitions 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 colu=
mn as a filter in where condition : trunc(order_date) =3D to_date(=E2=80=992=
022-04-01=E2=80=99,=E2=80=99YYYY-MM-DD=E2=80=99), the query scans all the pa=
rtitions 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=
 all 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