statistic target and sample rate

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
-

Responses

Browse pgsql-general by date

  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