Re: Performance issue - Seq Scan

Hello Guillaume!

As I highlighted the records count for these tables which are quite high,
would it be a best practice if we change the vacuum and analyze scale
factor at the table level?
Also, I am trying to understand if partitioning is required for these
tables, or at least for the one which has over 500million records?

Regards
Siraj

On Mon, Jan 20, 2025 at 3:04 PM Guillaume Lelarge <guillaume(at)lelarge(dot)info>
wrote:

> Hi,
>
> Le lun. 20 janv. 2025 à 09:42, Siraj G <tosiraj(dot)g(at)gmail(dot)com> 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.
>