pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHeap Only Update
2+ messages / 2 participants
[nested] [flat]
* Heap Only Update
@ 2018-01-02 12:16 Ali Mumcu <alimumcu1077@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Ali Mumcu @ 2018-01-02 12:16 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Heap Only Update
@ 2018-02-01 11:14 pavan95 <pavan.postgresdba@gmail.com>
parent: Ali Mumcu <alimumcu1077@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: pavan95 @ 2018-02-01 11:14 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-02-01 11:14 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-02 12:16 Heap Only Update Ali Mumcu <alimumcu1077@gmail.com>
2018-02-01 11:14 ` pavan95 <pavan.postgresdba@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox