From: Rod Taylor <pg@rbt.ca>
To: Greg Maples <gregm@nimblefish.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance - moving from oracle to postgresql
Date: Fri, 24 Jun 2005 18:00:52 -0400
Message-ID: <1119650452.45024.90.camel@home> (raw)
In-Reply-To: <2F9239F5E316C54688FDA9814FF045D6AB0024@EXCHANGE.sf.nimblefish.com>
References: <2F9239F5E316C54688FDA9814FF045D6AB0024@EXCHANGE.sf.nimblefish.com>
> 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.
--
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pg@rbt.ca, gregm@nimblefish.com
Subject: Re: Performance - moving from oracle to postgresql
In-Reply-To: <1119650452.45024.90.camel@home>
* 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