Re: BUG #16905: Dropping and recreating a large table with 5 indexes slowed down query performance

On Mon, 1 Mar 2021 at 23:32, PG Bug reporting form
<noreply(at)postgresql(dot)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.

From what you've provided, the best I can speculate would be that
auto-ANALYZE has executed and perhaps has picked a non-representative
set of blocks to scan and it thinks the (a,c) index is equally as good
as the (a,b) index. This is perhaps a little unlikely, but possible.

You may also not be aware that prior to PostgreSQL 13, auto-vacuum did
not run for INSERT-only tables (unless triggering for
anti-wraparound). Since you've created a new table and most likely
have only inserted records into it so far, auto-vacuum might not have
executed yet and you may be getting Index Scans where before you could
have been getting Index Only Scans. However, it's impossible to know
if that's the case based on the information you've sent so far.
auto-vacuum would mark "all visible" heap pages and thus reduce the
costing value the query planner puts on performing index only scans vs
index scans.

Further details might hint at something else is at fault.

David

[1] https://wiki.postgresql.org/wiki/Guide_to_reporting_problems