Thread: Heap Only Update
Hello Friends,
I am trying to find tables which Heap only update ratio less then 1 . Then i will decrease fillfactor on these tables for more speedly update queries.
I am using this query to find hot ratio. For you , this method true or false ?
select relname from pg_stat_user_tables where n_tup_upd>0 and n_tup_hot_upd > 0 and n_tup_hot_upd/n_tup_upd::float < 1;
Thanks .
Best Regards.
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