Skip site navigation (1) Skip section navigation (2)

Major differences between oracle and postgres performance - what can I do ?

From: Gary Cowell <gary_cowell(at)yahoo(dot)co(dot)uk>
To: pgsql-performance(at)postgresql(dot)org
Subject: Major differences between oracle and postgres performance - what can I do ?
Date: 2004-06-18 11:31:57
Message-ID: 20040618113157.84084.qmail@web25104.mail.ukl.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2004-06-18 12:07:15
Subject: Re: Major differences between oracle and postgres performance
Previous:From: SZUCS GáborDate: 2004-06-18 08:37:40
Subject: Re: *very* inefficient choice made by the planner (regarding

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group