postgres slower on nested queries - Mailing list pgsql-general

Hello,

I programmed a little script which iterates over all databases in a
DBMS, iterating over all tables and then over all columns.
This skript works for mysql and postgres.

1. Solution overview
====================

foreach database {
    foreach table {
        foreach column {
            do something ...
        }
    }
}

1. Solution detail
==================

foreach database
----------------
mysql: show databases
postgres: SELECT d.datname as Name FROM pg_catalog.pg_database d WHERE
(d.datname != 'template0' AND d.datname != 'template1') ORDER BY 1;)

foreach table
-------------
mysql: show tables
postgres: select table_name from information_schema.tables where
table_schema = 'public')

foreach column
--------------
mysql: show columns from '%s'
postgres: select column_name from information_schema.columns where
table_name = '%s')

(If there are better queries for postgres, please let me know.)

2. Solution
===========

I found the postgres version VERY slow, so a decided to fetch
    select table_name, column_name from information_schema.columns where
table_schema = 'public'

and wrote the output to an two dimensional array to process the elements
later:
    $tableA[$row['table_name']][$row['column_name']]=True;

The second approach ist much faster, this must be because there is no
nesting. ;(
Further - I think - there is no real caching done in the PHP Library of
xampp 1.5.4, php_pgsql.dll ;(

It would be very helpful what causes the problem.


Another question rises from this task:
======================================

Is there any possibility to use the shortcut \d as a query from PHP?
I used psql -E to display the query, but these queries rely on the
system catalogs, but the page:
http://www.postgresql.org/docs/8.2/interactive/information-schema.html
says they are not stable.
And - of course - it is much easier to type! ;)

Greetings,
    Alexander Elgert