Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 8BD929FB58C for ; Wed, 28 Mar 2007 09:59:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 90564-01 for ; Wed, 28 Mar 2007 09:59:21 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 36A1D9FB5E1 for ; Wed, 28 Mar 2007 09:59:21 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id k3so180460ugf for ; Wed, 28 Mar 2007 05:59:19 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=qJ4kaJ5lbUWgzo89+Z2RqmkPpkp/pm687D4Z2Nysj/DF+O+nXJHfeJDzJGPmr/7CHdB8ekFLCLombnpgN5upfvoVlMsfNpUOF5FCtuZDvqesiFFaJNH2EAv/dFy7t7e2p5HRauC9XQkNGHW5FO5d9VWLmydeNFqHD7+7hjyjmFo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=tE3q9WOGY0ZpKwM/1eT+RRGgTXIZSozYkDbrkP74oyBOIUzZyfiKmtVrMyj43GpRrADZfcwn7wYQSC193ILdojALdGBLrxxyTwDEwQQlC6htDbwPD45QiCD8xqqsM3X5l4ulIVhZ8DRu/NSgBDfFe5tmMZq9KF3ugt2d6xXw5+s= Received: by 10.114.179.1 with SMTP id b1mr3704238waf.1175086758258; Wed, 28 Mar 2007 05:59:18 -0700 (PDT) Received: by 10.115.72.5 with HTTP; Wed, 28 Mar 2007 05:59:18 -0700 (PDT) Message-ID: <48d0cacb0703280559g50610f2r67f189bec4185b73@mail.gmail.com> Date: Wed, 28 Mar 2007 09:59:18 -0300 From: "Daniel Cristian Cruz" To: pgsql-performance@postgresql.org Subject: Improving performance on system catalog MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_32124_27003850.1175086758206" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/408 X-Sequence-Number: 23813 ------=_Part_32124_27003850.1175086758206 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi all. I would like to speed up this query: EXPLAIN ANALYZE SELECT relid,schemaname,relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch,n_tup_ins,n_tup_upd,n_tup_del FROM pg_stat_user_tables; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan pg_stat_all_tables (cost=747.72..791.10 rows=195 width=236) (actual time=11.582..13.632 rows=200 loops=1) -> HashAggregate (cost=747.72..752.10 rows=195 width=136) (actual time= 11.571..12.813 rows=200 loops=1) -> Hash Join (cost=209.32..745.28 rows=195 width=136) (actual time=1.780..6.477 rows=453 loops=1) Hash Cond: ("outer".relnamespace = "inner".oid) -> Hash Left Join (cost=206.87..702.69 rows=227 width=76) (actual time=1.729..5.392 rows=507 loops=1) Hash Cond: ("outer".oid = "inner".indrelid) -> Seq Scan on pg_class c (cost=0.00..465.22 rows=227 width=72) (actual time=0.013..2.552 rows=228 loops=1) Filter: (relkind = 'r'::"char") -> Hash (cost=205.40..205.40 rows=587 width=8) (actual time=1.698..1.698 rows=0 loops=1) -> Seq Scan on pg_index i (cost=0.00..205.40rows=587 width=8) (actual time= 0.004..1.182 rows=593 loops=1) -> Hash (cost=2.44..2.44 rows=6 width=68) (actual time= 0.035..0.035 rows=0 loops=1) -> Seq Scan on pg_namespace n (cost=0.00..2.44 rows=6 width=68) (actual time=0.013..0.028 rows=6 loops=1) Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'pg_toast'::name)) Total runtime: 13.844 ms I think there would be good to create an index on pg_class.relkind and pg_class.relnamespace, but its impossible since its a catalog table. Any way to make it a default index (system index)? Its an old PostgreSQL server: SELECT version(); version ------------------------------------------------------------------------------------------------------------------------------ PostgreSQL 7.4.13 on x86_64-redhat-linux-gnu, compiled by GCC x86_64-redhat-linux-gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-2) -- Daniel Cristian Cruz Analista de Sistemas ------=_Part_32124_27003850.1175086758206 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi all.

I would like to speed up this query:

EXPLAIN ANALYZE
 SELECT relid,schemaname,relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch,n_tup_ins,n_tup_upd,n_tup_del
 FROM pg_stat_user_tables;

                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan pg_stat_all_tables  (cost= 747.72..791.10 rows=195 width=236) (actual time=11.582..13.632 rows=200 loops=1)
   ->  HashAggregate  (cost=747.72..752.10 rows=195 width=136) (actual time=11.571..12.813 rows=200 loops=1)
         ->  Hash Join  (cost= 209.32..745.28 rows=195 width=136) (actual time=1.780..6.477 rows=453 loops=1)
               Hash Cond: ("outer".relnamespace = "inner".oid)
               ->  Hash Left Join  (cost=206.87..702.69 rows=227 width=76) (actual time=1.729..5.392 rows=507 loops=1)
                     Hash Cond: ("outer".oid = "inner".indrelid)
                     ->  Seq Scan on pg_class c  (cost=0.00..465.22 rows=227 width=72) (actual time=0.013..2.552 rows=228 loops=1)
                           Filter: (relkind = 'r'::"char")
                     ->  Hash  (cost=205.40..205.40 rows=587 width=8) (actual time= 1.698..1.698 rows=0 loops=1)
                           ->  Seq Scan on pg_index i  (cost=0.00..205.40 rows=587 width=8) (actual time=0.004..1.182 rows=593 loops=1)
               ->  Hash  (cost=2.44..2.44 rows=6 width=68) (actual time= 0.035..0.035 rows=0 loops=1)
                     ->  Seq Scan on pg_namespace n  (cost=0.00..2.44 rows=6 width=68) (actual time=0.013..0.028 rows=6 loops=1)
                           Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'pg_toast'::name))
 Total runtime: 13.844 ms

I think there would be good to create an index on pg_class.relkind and pg_class.relnamespace, but its impossible since its a catalog table.

Any way to make it a default index (system index)?

Its an old PostgreSQL server:

SELECT version();
                                                           version
------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4.13 on x86_64-redhat-linux-gnu, compiled by GCC x86_64-redhat-linux-gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-2)



--
Daniel Cristian Cruz
Analista de Sistemas ------=_Part_32124_27003850.1175086758206--