From: | Luca Ferrari <fluca1978(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | statistic target and sample rate |
Date: | 2021-07-14 08:26:31 |
Message-ID: | CAKoxK+7TD9848q2wxuwdOw7b5WW+QDza2omScJw5EPJjkVzh+w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
I've a doubt about the sampling rate about the statistic target.
Documentation states that:
"The largest statistics target among the columns being analyzed
determines the number of table rows sampled to prepare the statistics.
Increasing the target causes a proportional increase in the time and
space needed to do ANALYZE."
My doubt is about how the "number of table rows sampled" is computed,
so let's do by example a single column table example:
testdb=> create table stat as select v from generate_series( 1, 1000000 ) v;
SELECT 1000000
testdb=> analyze verbose stat;
INFO: analyzing "public.stat"
INFO: "stat": scanned 4425 of 4425 pages, containing 1000000 live
rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total
rows
ANALYZE
The table starts with one million tuples, and statistic target at
default value (100). It samples 30k rows, that is roughly 1/3 pf the
table, while I was expecting 100k tuples (since I assume that the max
value for statistics target, 10000, correspnds to the whole table).
I can get a more close number of sampled rows considering the sampling
on the pages of the relation, but I'm not sure this is the right way
it operates:
testdb=> alter table stat alter column v set statistics 500;
ALTER TABLE
testdb=> analyze verbose stat;
INFO: analyzing "public.stat"
INFO: "stat": scanned 4425 of 4425 pages, containing 1000000 live
rows and 0 dead rows; 150000 rows in sample, 1000000 estimated total
rows
ANALYZE
testdb=> select reltuples / relpages::real * attstattarget from
pg_class c join pg_attribute a on a.attrelid = c.oid where c.relname =
'stat' and a.attname = 'v';
?column?
--------------------
112994.34661865234
(1 riga)
Therefore my question is about how the statistic collectore decides
about the number of tuples to be sampled.
Thanks,
Luca
-
From | Date | Subject | |
---|---|---|---|
Next Message | Luca Ferrari | 2021-07-14 08:43:59 | Re: pg_wal lifecycle |
Previous Message | Laurenz Albe | 2021-07-14 05:32:52 | Re: Need to check each element of an array satisfies a foreign key constraint |