Re: postgres slower on nested queries - Mailing list pgsql-general

Alexander Elgert wrote:
> 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 ...
>        }
>    }
> }

OK - obviously not the way to do it if you really want all the columns
in all the databases...

> 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;)

You can have template databases called other names too of course.

> 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

Define VERY - it took what, milliseconds to do this? Seconds? Hours?

>    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;

Not sure what this code is meant to show.

> The second approach ist much faster, this must be because there is no
> nesting. ;(

What nesting? Are you trying to do sub-queries of some sort?

In your first example, you were querying for individual rows, in the
second you fetch them all at once. Of course it's quicker to run one
query and return many rows than many queries each returning one row.
Otherwise your RDBMS has something seriously wrong with it.

> Further - I think - there is no real caching done in the PHP Library of
> xampp 1.5.4, php_pgsql.dll ;(

No (not that I'm sure what xampp is). Look into memcache/pg_memcache if
you want caching - that does it properly.

> It would be very helpful what causes the problem.

You haven't actually described a problem afaict. Can you describe what
it is you're trying to do? Do you want:
   1. Details of a single column
   2. To loop through all the columns doing something
If you can say which of these you're trying to do, and what the
"something" is, then I'm sure there's somebody here who can help.

> 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! ;)

Nope - \d is psql only. That's because what it does changes with the
system catalogues. If you want a stable representation, use the
information_schema. If you want all the PostgreSQL-specific details
you'll have to cope with changes to the system catalogues. However, I
seem to remember some utility views that pgadmin uses that might be of use.

Oh - and you might find phppgadmin useful to look at.

--
   Richard Huxton
   Archonet Ltd