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

Re: Performance - moving from oracle to postgresql

From: Rod Taylor <pg(at)rbt(dot)ca>
To: Greg Maples <gregm(at)nimblefish(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance - moving from oracle to postgresql
Date: 2005-06-24 22:00:52
Message-ID: 1119650452.45024.90.camel@home (view raw or flat)
Thread:
Lists: pgsql-performance
> 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.

-- 


In response to

pgsql-performance by date

Next:From: Dmitri BichkoDate: 2005-06-24 22:02:36
Subject: Re: Performance Tuning Article
Previous:From: Todd LandfriedDate: 2005-06-24 21:18:24
Subject: Re: Needed: Simplified guide to optimal memory configuration

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