Date: Tue, 18 Dec 2018 23:04:40 -0700 (MST)
From: anand086 <anand086@gmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1545199480113-0.post@n3.nabble.com>
Subject: SQL Perfomance during autovacuum
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi All,

I am looking into a performance issue and needed your input and thoughts.

We have table (non-partitioned) of 500Gb with 11 indexes 

+--------------+---------------+--------------+-------------+--------------+---------+--------+------------+--------+

| row_estimate |  total_bytes  | index_bytes  | toast_bytes | table_bytes  | 
total  | index  |   toast    | table  |

+--------------+---------------+--------------+-------------+--------------+---------+--------+------------+--------+

|  1.28611e+09 | 1400081645568 | 858281418752 |        8192 | 541800218624 |
1304 GB | 799 GB | 8192 bytes | 505 GB |

+--------------+---------------+--------------+-------------+--------------+---------+--------+------------+--------+


Application runs a simple sql ,

select distinct testtbl_.id as col_0_0_ from demo.test_table testtbl_ where
testtbl_.entity_id='10001' and testtbl_.last_updated>=to_date('22-10-2018',
'dd-MM-yyyy') and testtbl_.last_updated<to_date('23-10-2018', 'dd-MM-yyyy')
and testtbl_.quantity_available>0 and testtbl_.src_name='distribute_item'
and (testtbl_.item not like 'SHIP%') order by testtbl_.id limit 10000;

The Execution time for the above sql is  17841.467 ms during normal
operations but when autovacuum runs on table test_table, the same sql took
1628495.850 ms (from the postgres log). 

We have noticed this increase in execution times for the sqls only when
autovacuum runs and it runs with prevent wraparound mode. I think during the
autovacuum process the Buffers: shared hit are increasing causing increase
in execution time.

I need help with the approach to debug this issue. Is this expected
behaviour wherein sql execution timing incease during the autovacuum? If so
, what is the reason for the same? 


--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html