On Mon, Nov 5, 2012 at 9:04 PM, Lonni J Friedman <netllama@gmail.com> wrote:
On Mon, Nov 5, 2012 at 3:56 PM, Thalis Kalfigkopoulos
<tkalfigo@gmail.com> wrote: > > 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?
doh, sorry. The first/larger doesn't work. As it turns out the 2nd actually does work well. I'm on 9.1.x.
That's weird. I'm on a 9.1.5 as well and it works fine (!?) Still the question remains: are they equivalent?