Search Postgresql Archives

Re: Quick estimate of num of rows & table size

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Nov 5, 2012 at 3:56 PM, Thalis Kalfigkopoulos
<tkalfigo@xxxxxxxxx> wrote:
>
> On Mon, Nov 5, 2012 at 7:14 PM, Lonni J Friedman <netllama@xxxxxxxxx> wrote:
>>
>> On Mon, Nov 5, 2012 at 2:02 PM, Thalis Kalfigkopoulos
>> <tkalfigo@xxxxxxxxx> 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.


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux