Skip site navigation (1) Skip section navigation (2)

Slow queries from information schema

From: Octavio Alvarez <alvarezp(at)alvarezp(dot)ods(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Slow queries from information schema
Date: 2009-02-14 18:13:07
Message-ID: 1234635187.17840.27.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-performance
I'm aware you already know that information_schema is slow [1] [2], so I
just want to expose/document another case and tests I did.

I'm using the following view to check what tables depend on what other
tables.

CREATE VIEW raw_relation_tree AS
SELECT
   tc_p.table_catalog AS parent_catalog,
   tc_p.table_schema AS parent_schema,
   tc_p.table_name AS parent_table,
   tc_c.table_catalog AS child_catalog,
   tc_c.table_schema AS child_schema,
   tc_c.table_name AS child_table
FROM
   information_schema.referential_constraints AS rc
   NATURAL JOIN information_schema.table_constraints AS tc_c
   LEFT JOIN information_schema.table_constraints AS tc_p ON
      rc.unique_constraint_catalog = tc_p.constraint_catalog AND
      rc.unique_constraint_schema = tc_p.constraint_schema AND
      rc.unique_constraint_name = tc_p.constraint_name
;

test=# select count(*) from raw_relation_tree;
count 
-------
    11
(1 row)

An EXPLAIN ANALYZE for a simple SELECT on each of the FROM tables give:
referential_constraints: ~9ms.
table_constraints: ~24ms.

The result, on the above view: ~80ms. Fair enough. But if I apply a
condition:

SELECT * FROM ___pgnui_relation_tree.raw_relation_tree WHERE
parent_schema <> child_schema;

it takes ~2 seconds (!) to complete.

I tried using an alternate table_constraints definition by creating my
own view and changing UNION to UNION ALL (as per [2]) The results were:

table_constraints using UNION ALL has the same number of rows as the
UNION version.

table_constraints now take about 4 ms (as expected).
VIEW raw_relation_tree is now 110 ms.
VIEW raw_relation_tree WHERE parent_schema <> child_schema: 3.3 sec.

EXPLAIN results are way too long to post here. If it is ok, I'll gladly
post them.

Using 8.3.6.

[1] http://archives.postgresql.org/pgsql-bugs/2008-12/msg00144.php
[2]
http://archives.postgresql.org/pgsql-performance/2008-05/msg00062.php



Responses

pgsql-performance by date

Next:From: Alexander StauboDate: 2009-02-14 18:26:37
Subject: Re: I/O increase after upgrading to 8.3.5
Previous:From: Peter G.Date: 2009-02-14 16:11:43
Subject: Retrieving data from PostgreSQL to .NET application ?==?windows-1252?Q?– performance test – surprising results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group