Обсуждение: Heap Only Update

Hi Ali,

Before changing the default value of Fillfactor, we should measure the size
of Table Row.

For knowing the row size is very important because if table row size is
larger, we should not change the default value of Fillfactor.

When we are doing performance optimization, this is very important to find
the size of the Data page and Table row, otherwise unnecessary we are
dealing with high fragmentation and executing VACUUM FULL or VACUUM again
and again.

The default Fillfactor is 100 and it is better, but not in all situations.
*When your table has frequent updates, this is not a better solution because
it requires more CPU and IO for different data page operations which
actually degrade the performance*.

The solution is to first measure the size of the tuple and if tuple size is
not that much bigger, we can reduce the value of default Fillfactor.

If your total row size is under 8kb, you can take decision to alter table
storage parameters.

You can use below script to measure the size of the tuple and if that size
is not that big you can go ahead in decreasing the value of fill factor:

WITH cteTableInfo AS 
(
    SELECT 
        COUNT(1) AS ct
        ,SUM(length(t::text)) AS TextLength  
        ,'public.your_table_name'::regclass AS TableName  
    FROM public.your_table_name AS t  
)
,cteRowSize AS 
(
   SELECT ARRAY [pg_relation_size(TableName)
               , pg_relation_size(TableName, 'vm')
               , pg_relation_size(TableName, 'fsm')
               , pg_table_size(TableName)
               , pg_indexes_size(TableName)
               , pg_total_relation_size(TableName)
               , TextLength
             ] AS val
        , ARRAY ['Total Relation Size'
               , 'Visibility Map'
               , 'Free Space Map'
               , 'Table Included Toast Size'
               , 'Indexes Size'
               , 'Total Toast and Indexes Size'
               , 'Live Row Byte Size'
             ] AS Name
   FROM cteTableInfo
)
SELECT 
    unnest(name) AS Description
    ,unnest(val) AS Bytes
    ,pg_size_pretty(unnest(val)) AS BytesPretty
    ,unnest(val) / ct AS bytes_per_row
FROM cteTableInfo, cteRowSize
 
UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'TotalRows', ct, NULL, NULL FROM cteTableInfo
UNION ALL SELECT 'LiveTuples', pg_stat_get_live_tuples(TableName), NULL,
NULL FROM cteTableInfo
UNION ALL SELECT 'DeadTuples', pg_stat_get_dead_tuples(TableName), NULL,
NULL FROM cteTableInfo;


Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html