Re: execution plan : Oracle vs PostgreSQL

my first implementation was in pl/pgsql but when i query the children of a node, i need to store them into an array because i need to iterate over all the children and for each child, I test the type of it.
if it's a PI or a TEXT, i write it into a file, but if it's an element, i call the same function with new parameters (recursive call) and in consequence i can't use a cursor.

in pl/pgsql, the result of a query is returned into a cursor, and in my implementation the only solution i found was to iterate over the cursor and to add children into an array.
i didn't found any solution to get all the children directly into an array (like the oracle BULK COLLECT).
So we chose pl/perl.

maybe there is an other way to query children directly into an array and having query plan caching ?

-----Message d'origine-----
De : Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Envoyé : mercredi 1 février 2006 17:05
À : FERREIRA, William (VALTECH)
Cc : pgsql-performance(at)postgresql(dot)org
Objet : Re: [PERFORM] execution plan : Oracle vs PostgreSQL

"FERREIRA, William (VALTECH)" <william(dot)ferreira(at)airbus(dot)com> writes:
> My test document has 115000 nodes.
> the export of the document(extracting all informations from database and writing XML file on disk) takes 30s with Oracle and 5mn with Postgresql.
> The Oracle stored procedure is written in pl/sql and the Postgresql stored procedure in pl/perl (using spi_exec).

So the test case involves 115000 executions of the same query via spi_exec?
That means the query will be re-parsed and re-planned 115000 times. If
you want something that's a reasonably fair comparison against Oracle,
try plpgsql which has query plan caching.

regards, tom lane

PS: please do NOT post EXPLAIN VERBOSE output unless someone
specifically asks for it. It clutters the archives and it's usually
useless. EXPLAIN ANALYZE is what we normally want to see for
performance issues.

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.
Security Notice: all e-mail, sent to or from this address, may be
accessed by someone other than the recipient, for system management and
security reasons. This access is controlled under Regulation of
Investigatory Powers Act 2000, Lawful Business Practises.