pg.ddx.io pgsql-bugs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
BUG #16904: Dropping and recreating a large table with 5 indexes slowed down query performance 2+ messages / 2 participants [nested] [flat]
* BUG #16904: Dropping and recreating a large table with 5 indexes slowed down query performance @ 2021-03-01 08:58 PG Bug reporting form <noreply@postgresql.org> 2021-03-01 14:40 ` Re: BUG #16904: Dropping and recreating a large table with 5 indexes slowed down query performance Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: PG Bug reporting form @ 2021-03-01 08:58 UTC (permalink / raw) To: pgsql-bugs@lists.postgresql.org; +Cc: tejaschavanr10@gmail.com The following bug has been logged on the website: Bug reference: 16904 Logged by: tejas chavan Email address: tejaschavanr10@gmail.com PostgreSQL version: 10.15 Operating system: Ubuntu 10.15-0ubuntu0.18.04.1 Description: Hi, We performed a data purging activity as the database server space was almost full and needed to free up some space. Table stats: table size: 580 GB Number of Indexes: 5 Indexes: 1. btree (col_a, col_b) 2. btree (col_a, col_c) 3. EXCLUDE USING gist (col_a WITH =, col_c WITH =, col_d WITH =, tstzrange(col_e, col_f, ‘[)’::text) WITH &&) WHERE (col_d IS NOT NULL) 4. EXCLUDE USING gist (col_a WITH =, col_c WITH =, col_g WITH =, col_h WITH =, tstzrange(col_e, col_f, ‘[)’::text) WITH &&) WHERE (col_g IS NOT NULL AND col_h IS NOT NULL) 5. EXCLUDE USING gist (col_a WITH =, col_c WITH =, tstzrange(col_e, col_f, ‘[)’::text) WITH &&) WHERE (col_d IS NULL AND col_g IS NULL AND col_h IS NULL) Total index size: 1.2 TB The steps performed are as below: - Renamed the original table as _bkp - Dropped indexes and constraints on the backup table - Created the original table along with indexes with the same index name as before Post performing this activity, the queries which where performing well before are running very slow. After re-creating the table, for next 2 days, the performance of the query was same as before (i.e. 20 minutes) but later it's taking more than 1 hour to run. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #16904: Dropping and recreating a large table with 5 indexes slowed down query performance 2021-03-01 08:58 BUG #16904: Dropping and recreating a large table with 5 indexes slowed down query performance PG Bug reporting form <noreply@postgresql.org> @ 2021-03-01 14:40 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2021-03-01 14:40 UTC (permalink / raw) To: tejaschavanr10@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org PG Bug reporting form <noreply@postgresql.org> writes: > The steps performed are as below: > - Renamed the original table as _bkp > - Dropped indexes and constraints on the backup table > - Created the original table along with indexes with the same index name as > before > Post performing this activity, the queries which where performing well > before are running very slow. Perhaps ANALYZE'ing the new table would help. I'd have expected autovacuum to do that for you by now, but maybe it's not able to complete for some reason. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-03-01 14:40 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-03-01 08:58 BUG #16904: Dropping and recreating a large table with 5 indexes slowed down query performance PG Bug reporting form <noreply@postgresql.org> 2021-03-01 14:40 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox