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)
Date: Wed, 24 Aug 2022 15:41:08 -0700
Subject: Performance issue after creating partitions 
Message-Id: <10FAC7AB-09D1-4013-BB3C-79AA3AEDE374@gmail.com>
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/10FAC7AB-09D1-4013-BB3C-79AA3AEDE374%40gmail.com>
Precedence: bulk

Hello Admin team,

We have a Postgres 13 server set up on Google Cloud. Recently we created par=
titions 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.

Order date column is a time stamp column and when we are using this column a=
s a filter in where condition : trunc(order_date) =3D to_date(=E2=80=992022-=
04-01=E2=80=99,=E2=80=99YYYY-MM-DD=E2=80=99), the query scans all the partit=
ions dated back from the year 2000. So, instead of going to the 2022 Q2 part=
ition 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 corr=
ect 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 way b=
etter.

Thanks,
J. Teja.=