Re: [PERFORM] Performance - moving from oracle to postgresql
> 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." I agree with Rod: you are correct but this is a very odd objection. You are declaring a set but are only interested in a tiny subset of that based on arbitrary critera. You can do this with cursors or with clever querying (not without materializing the full set however), but why? Merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Performance - moving from oracle to postgresql
> "- 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. When I was reading up on resultset pagination on AskTom I got a clear impression that the same happens in Oracle as well. Resultset is like: 0START...STOP...END 0STOP START...END You first select all the rows from 0 to STOP and then from that select the rows from START to end (which is now STOP). This is done using ROWNUM twice and subselects. It was discussed over there that this obviously produces higher response times as you move towards the end of a very large resultset. Tom even pointed out the same effect when using google search, as you move forward through a very large (millions) search result. Regards, -- Radu-Adrian Popescu CSA, DBA, Developer Aldrapay MD Aldratech Ltd. +40213212243 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Performance - moving from oracle to postgresql
> 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])
[PERFORM] Performance - moving from oracle to postgresql
Hi: I'm beginning the push at our company to look at running postgreSQL in production here. We have a dual CPU 2.8 GHZ Xeon Box running oracle. Typical CPU load runs between 20% and 90%. Raw DB size is about 200GB. We hit the disk at roughly 15MB/s read volume and 3MB/s write. At any given time we have from 2 to 70 sessions running on the instance. Sessions often persist for 24 hours or more. Total FreeFree Mb Mb % IDXS_EXT10 2000290 14.5 DATA_EXT1001 3200 32 SYSTEM 220 95.2 43.3 IDXS_EXT1002 9600 48 DATA_EXT10 6000 2990 49.8 UNDOB4000 2561.1 64 TEMP 8000 5802.9 72.5 DATA_LOB_EXT20 2000 1560 78 IDXS_EXT1 500 40180.2 DATA_EXT1 4000 3758 94 Total Instance 56720 30258.2 53.3 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." What are the key performance areas I should be looking at? Where is psql not appropriate to replace Oracle? Thanks in advance, apologies if this occurs as spam, please send Replies to me off-list. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
