From: Gary Cowell <gary_cowell@yahoo.co.uk>
To: pgsql-performance@postgresql.org
Subject: Major differences between oracle and postgres performance - what can I do ?
Date: Fri, 18 Jun 2004 12:31:57 +0100 (BST)
Message-ID: <20040618113157.84084.qmail@web25104.mail.ukl.yahoo.com> (raw)
I'm trying to migrate an application from an Oracle
backend to PostgreSQL and have a performance question.
The hardware for the database is the same, a SunFire
v120, 2x73GB U2W SCSI disks, 1GB RAM, 650MHz US-IIe
CPU. Running Solaris 8.
The table in question has 541741 rows. Under Oracle,
the query ' select distinct version from vers where
version is not null ' returns 534 rows in 6.14
seconds, with an execution plan showing a table scan
of vers followed by a sort.
The explain output on postgres shows the same
execution with a scan on vers and a sort but the query
time is 78.6 seconds.
The explain output from PostgreSQL is:
QUERY PLAN
---------------------------------------------------------------------------------
Unique (cost=117865.77..120574.48 rows=142
width=132)
-> Sort (cost=117865.77..119220.13 rows=541741
width=132)
Sort Key: "version"
-> Seq Scan on vers (cost=0.00..21367.41
rows=541741 width=132)
Filter: ("version" IS NOT NULL)
I do have an index on the column in question but
neither oracle nor postgresql choose to use it (which
given that we're visiting all rows is perhaps not
surprising).
I'm not as familiar with postgresql as I am with
Oracle but I think I've configured comparible
buffering and sort area sizes, certainly there isn't
much physical IO going on in either case.
What can I do to speed up this query? Other queries
are slightly slower than under Oracle on the same
hardware but nothing like this.
Thanks!
G
___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to express yourself http://uk.messenger.yahoo.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: gary_cowell@yahoo.co.uk
Subject: Re: Major differences between oracle and postgres performance - what can I do ?
In-Reply-To: <20040618113157.84084.qmail@web25104.mail.ukl.yahoo.com>
References: <20040618113157.84084.qmail@web25104.mail.ukl.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox