Re: vacuum verbose relations reporting

Jeff Frost wrote:
> Alvaro Herrera wrote:
>> Jeff Frost wrote:
>>
>>
>>> Tom, is there an easy (or hard) way to count relations from all DBs by
>>> using the system catalogs?
>>>
>>
>> Just do a count(*) from pg_class where relkind in ('r', 't', 'i'), and
>> sum across all databases (you need to connect to each one).
>>
>> (Actually you only need to count indexes that are btrees, if you need
>> such a distinction. Other indexes do not use the FSM as far as I know).
>>
> Perfect, so here's a little script that does the trick then:
>
> #!/bin/sh
>
> PSQL=/usr/bin/psql
> DATABASES=$($PSQL -lt | awk {'print $1'} | grep -v template0 )
> RELATIONS=0
>
> for DB in $DATABASES; do
> RELATIONS=$(($RELATIONS + $($PSQL --tuples-only --command "select
> count(*) from pg_class where relkind IN ('r', 't', 'i');" $DB) ))
> done
>
> echo $RELATIONS
>
I guess this isn't entirely accurate, as the above script returns 35883,
but vacuum verbose returns:

INFO: free space map contains 111435 pages in 10005 relations

If I take out the toast tables and indexes, I get a result much closer
to what vacuum verbose returns: 10626 which might just be because the
vacuum verbose ran a few hours ago.

So, the question is, do the FSM settings take into account toast tables
and indexes as Alvaro suggested and vacuum verbose isn't properly
reporting on it?

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032