On Mon, Nov 5, 2012 at 7:14 PM, Lonni J Friedman <netllama@gmail.com> wrote:
On Mon, Nov 5, 2012 at 2:02 PM, Thalis Kalfigkopoulos <tkalfigo@gmail.com> wrote: > 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.
This doesn't seem to work for me. I get an estimated row_count of 0 on a table that I know has millions of rows.
Which one doesn't work exactly? The larger query? Are you on a 9.x?
