From: | Alexander Elgert <alexander_elgert(at)adiva(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | postgres slower on nested queries |
Date: | 2007-03-06 20:02:26 |
Message-ID: | 45EDC8D2.1000209@adiva.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Nik | 2007-03-06 20:19:28 | Re: No buffer space available |
Previous Message | Peter Eisentraut | 2007-03-06 19:20:36 | Re: vacuum error |