Re: Performance issue - Seq Scan

[Guillaume Lelarge <guillaume@xxxxxxxxxxxx>]

Hi,

Le lun. 20 janv. 2025 à 09:42, Siraj G <tosiraj.g@xxxxxxxxx> a écrit :

Hello Experts!

We had a performance issue with a SQL that used to complete in a few milliseconds, was taking over 14seconds. We had to run analyze on 3 tables to get the idle performance back. 

When the performance was not optimal, we noticed sequential scans even with indexes created.

The tables and their count:

coverage_details = 529628595
customer_details = 81721669
policy_details = 116909729

PgSQL version is:

PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit

One more information is that we noticed this started happening (in the destination) after an ETL job completed the load (regular load). Just wanted to know if any follow up actions we should do after such data loads, eg., analyze or vacuum. We do have autovacuum on, with default values.

Yes, you should run "VACUUM ANALYZE" after running a batch. autovacuum could be not fast enough to do it itself before you start querying the new data.

--

Guillaume.