Re: \d on database with a lot of tables is slow

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: Hannu Krosing <hannu(at)skype(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: \d on database with a lot of tables is slow
Date: 2005-10-01 18:00:12
Message-ID: 9615.1128189612@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I wrote:
> It's presumably mostly in the pg_table_is_visible() calls.

I did some profiling on a test case with 10000 tables, and noticed that
a big part of the problem is that the catalog caches become entirely
useless: almost every catcache lookup ends up going to the underlying
tables. This is because MAXCCTUPLES in catcache.c is fixed at 5000,
and that's not an adequate working set for this many tables. If you
are willing to throw memory at the problem, you could try increasing
MAXCCTUPLES (to say 50K or 100K) and see if that helps.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2005-10-01 18:10:41 Re: [PATCHES] Proposed patch for sequence-renaming problems
Previous Message Ron Peacetree 2005-10-01 17:42:32 Re: [HACKERS] A Better External Sort?