pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16905: Dropping and recreating a large table with 5 indexes slowed down query performance
2+ messages / 2 participants
[nested] [flat]
* BUG #16905: Dropping and recreating a large table with 5 indexes slowed down query performance
@ 2021-03-01 09:08 PG Bug reporting form <noreply@postgresql.org>
2021-03-02 03:19 ` Re: BUG #16905: Dropping and recreating a large table with 5 indexes slowed down query performance David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2021-03-01 09:08 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: tejaschavanr10@gmail.com
The following bug has been logged on the website:
Bug reference: 16905
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.
After analyzing the query plan, it is observed when I am filtering based on
col_a and col_b, the query planner is scanning a composite index having
col_a and col_c. However I also have composite index of col_a and col_b. But
query planner is not picking it.
[Note: There are no other changes in the database structure apart from the
mentioned ones]
Kindly assist in tracking the root cause of the scenario and suggest the
approaches to improve the performance of the query.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #16905: Dropping and recreating a large table with 5 indexes slowed down query performance
2021-03-01 09:08 BUG #16905: Dropping and recreating a large table with 5 indexes slowed down query performance PG Bug reporting form <noreply@postgresql.org>
@ 2021-03-02 03:19 ` David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: David Rowley @ 2021-03-02 03:19 UTC (permalink / raw)
To: tejaschavanr10@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, 1 Mar 2021 at 23:32, PG Bug reporting form
<noreply@postgresql.org> wrote:
> 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.
It might trigger more people to look at this if you include the
EXPLAIN ANALYZE output of the query along with the \d+ output of the
table(s) that the query runs against.
Please see [1] for more details on what else might be useful.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-03-02 03:19 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 09:08 BUG #16905: Dropping and recreating a large table with 5 indexes slowed down query performance PG Bug reporting form <noreply@postgresql.org>
2021-03-02 03:19 ` David Rowley <dgrowleyml@gmail.com>
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