> 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. -- ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])