Re: slow "select count(*) from information_schema.tables;" in some cases

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no>
Cc: Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com>, Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Re: slow "select count(*) from information_schema.tables;" in some cases
Date: 2022-02-07 19:02:50
Message-ID: 1016661.1644260570@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no> writes:
>> SELECT relname, pg_size_pretty(pg_relation_size(C.oid)) FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname = 'pg_catalog' ORDER BY 2 DESC LIMIT 20; can you show the output of this query

"ORDER BY 2" is giving you a textual sort of the sizes, which is entirely
unhelpful. Try

SELECT relname, pg_size_pretty(pg_relation_size(C.oid)) FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname = 'pg_catalog' ORDER BY pg_relation_size(C.oid) DESC LIMIT 20;

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Lars Aksel Opsahl 2022-02-07 19:11:58 Re: slow "select count(*) from information_schema.tables;" in some cases
Previous Message Lars Aksel Opsahl 2022-02-07 18:55:57 Re: slow "select count(*) from information_schema.tables;" in some cases