X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 4A6569DCA32 for ; Wed, 1 Feb 2006 12:04:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 73714-06 for ; Wed, 1 Feb 2006 12:04:47 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 6385D9DCA15 for ; Wed, 1 Feb 2006 12:04:46 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k11G4ida000902; Wed, 1 Feb 2006 11:04:44 -0500 (EST) To: "FERREIRA, William (VALTECH)" cc: pgsql-performance@postgresql.org Subject: Re: execution plan : Oracle vs PostgreSQL In-reply-to: <414D259CE29DE54DAD534037C83CE4B726961D@FR0-MAILMB20.res.airbus.corp> References: <414D259CE29DE54DAD534037C83CE4B726961D@FR0-MAILMB20.res.airbus.corp> Comments: In-reply-to "FERREIRA, William (VALTECH)" message dated "Wed, 01 Feb 2006 11:11:33 +0100" Date: Wed, 01 Feb 2006 11:04:44 -0500 Message-ID: <901.1138809884@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.102 required=5 tests=[AWL=0.102] X-Spam-Score: 0.102 X-Spam-Level: X-Archive-Number: 200602/14 X-Sequence-Number: 17000 "FERREIRA, William (VALTECH)" 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.