Subject: Re: Performance - moving from oracle to postgresql
From: Rod Taylor <pg@rbt.ca>
To: Greg Maples <gregm@nimblefish.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: 
 <2F9239F5E316C54688FDA9814FF045D6AB0024@EXCHANGE.sf.nimblefish.com>
References: 
 <2F9239F5E316C54688FDA9814FF045D6AB0024@EXCHANGE.sf.nimblefish.com>
Content-Type: text/plain
Date: Fri, 24 Jun 2005 18:00:52 -0400
Message-Id: <1119650452.45024.90.camel@home>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


> There are some immediate questions from our engineers about performance
> 
> "- Oracle has one particular performance enhancement that Postgres is
> missing.  If you do a select that returns 100,000 rows in a given order,
> and all you want are rows 99101 to 99200, then Oracle can do that very
> efficiently.  With Postgres, it has to read the first 99200 rows and
> then discard the first 99100.  But...  If we really want to look at
> performance, then we ought to put together a set of benchmarks of some
> typical tasks."
> 
> Is this accurate:
> accoring to
> http://www.postgresql.org/docs/8.0/interactive/queries-limit.html
>   -- " The rows skipped by an OFFSET clause still have to be computed 
> inside the server; therefore a large OFFSET can be inefficient."

Yes. That's accurate. First you need to determine whether PostgreSQLs
method is fast enough for that specific query, and if the performance
gains for other queries (inserts, updates, delete) from reduced index
management evens out your concern. All performance gains through design
changes either increase complexity dramatically or have a performance
trade-off elsewhere.


I find it rather odd that anyone would issue a single one-off select for
0.1% of the data about 99.1% of the way through, without doing anything
with the rest. Perhaps you want to take a look at using a CURSOR?

> Where is psql not appropriate to replace Oracle?

Anything involving reporting using complex aggregates or very long
running selects which Oracle can divide amongst multiple CPUs.

Well, PostgreSQL can do it if you give it enough time to run the query,
but a CUBE in PostgreSQL on a TB sized table would likely take
significantly longer to complete. It's mostly just that the Pg
developers haven't implemented those features optimally, or at all, yet.

--