Quick estimate of num of rows & table size
От | Thalis Kalfigkopoulos |
---|---|
Тема | Quick estimate of num of rows & table size |
Дата | |
Msg-id | CAEkCx9F6mu7gmxAw_8=sA4HQRUmYruRkN77wRxtd7tvhEuTL3A@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Quick estimate of num of rows & table size
Re: Quick estimate of num of rows & table size |
Список | pgsql-general |
Hi all,
I read somewhere that the following query gives a quick estimate of the # of rows in a table regardless of the table's size (which would matter in a simple SELECT count(*)?):
SELECT (CASE WHEN reltuples > 0 THEN
pg_relation_size('mytable')/(8192*relpages/reltuples)
ELSE 0
END)::bigint AS estimated_row_count
FROM pg_class
WHERE oid = 'mytable'::regclass;
If relpages & reltuples are recorded accurately each time VACUUM is run, wouldn't it be the same to just grab directly the value of reltuples like:
SELECT reltuples FROM pg_class WHERE oid='mytable'::regclass;
In the same manner, are pg_relation_size('mytable') and 8192*relpages the same?
I run both assumptions against a freshly VACUUMed table and they seem correct.
TIA,
Thalis K.
I read somewhere that the following query gives a quick estimate of the # of rows in a table regardless of the table's size (which would matter in a simple SELECT count(*)?):
SELECT (CASE WHEN reltuples > 0 THEN
pg_relation_size('mytable')/(8192*relpages/reltuples)
ELSE 0
END)::bigint AS estimated_row_count
FROM pg_class
WHERE oid = 'mytable'::regclass;
If relpages & reltuples are recorded accurately each time VACUUM is run, wouldn't it be the same to just grab directly the value of reltuples like:
SELECT reltuples FROM pg_class WHERE oid='mytable'::regclass;
In the same manner, are pg_relation_size('mytable') and 8192*relpages the same?
I run both assumptions against a freshly VACUUMed table and they seem correct.
TIA,
Thalis K.
В списке pgsql-general по дате отправления: