0

I tried to create indexes on a Postgres partitioned table which contains around 1.3 million rows. It contains data for the year 2023 and I partitioned it on the 'created_at' column which is of timestamp type. But still, when I try to query data for 2-3 months it's taking 3-4 minutes to return the resulting rows, and takes around 15-20 seconds if I query for 4-5 days of data. So I thought of creating an index on the parent table 'ticket_partition' which is further partitioned for every month having table names m1 to m11: Table Schema I have come to know the below thread which explains how we can create an index on the partitioned tables: create index concurrently on partitioned table

Then I created index according to this and when I tried to merge the partitioned table's index to the parent table's index, it's saying 'relation "tkt_idx" does not exist' as shown in the below image

error message Does anyone knows how we can resolve this error? Also if you guys have any other way to improve the query performance i.e. retrieve data for 2-3 months within a minute then please share your thoughts as well. Thanks

Tried Creating index on Partitioned tables and then attaching it to parent table's index but not working

Nishant Jaijan
  • 1
  • 1
    Could you please share the external images in plain text in your question? And when having performance issues, please share the result from `explain(analyze, verbose, buffers, settings)` for your SQL statement, the SQL statement itself and the DDL for all tables and indexes involved. All in plain text, as an update to your question. – Frank Heikens Aug 10 '23 at 15:15

0 Answers0