[PERFORM] Repeated Query is much slower in PostgreSQL8.2.4 than DB2 9.1
Hi, I am transplanting an application to use PostgreSQL8.2.4 instead of DB2 9.1. CLI was used to connect to DB2, and ODBC is used to connect to PostgreSQL. The query statement is as follows: SELECT void,nameId,tag FROM (SELECT void,nameId,tag, FROM Attr WHERE attributeof IN (SELECT oid_ FROM ItemView WHERE ItemView.ItemId=?)) x RIGHT OUTER JOIN (SELECT oid_ FROM ItemView WHERE ItemView.ItemId=? and ItemView.assignedTo_=?) y ON attributeof = oid_ FOR READ ONLY I tested the performance on PostgreSQL against DB2, and found that First execution: PostgreSQL 0.006277 seconds / DB2 0.009028 seconds Second execution: PostgreSQL 0.005932 seconds / DB2 0.000332 seconds PostgreSQL cost nearly the same time but DB2 ran 30 times faster in second execution. I tried to adjust shared_buffers parameter in postgresql.conf, no speed up. Actually my DB holds only several records so I don't think memory is the reason. Could anybody give some advice to speed up in repeated execution in PostgreSQL or give an explanation why DB2 is so mush faster in repeated execution? Thank you. ning -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Repeated Query is much slower in PostgreSQL8.2.4 than DB2 9.1
Hi Craig, The log is really long, but I compared the result of "explain analyze" for first and later executions, except for 3 "time=XXX" numbers, they are identical. I agree with you that PostgreSQL is doing different level of caching, I just wonder if there is any way to speed up PostgreSQL in this scenario, which is a wrong way perhaps. Thank you. Ning On Wed, Jul 15, 2009 at 5:27 PM, Craig Ringer wrote: > On Wed, 2009-07-15 at 12:10 +0900, ning wrote: > >> First execution: PostgreSQL 0.006277 seconds / DB2 0.009028 seconds >> Second execution: PostgreSQL 0.005932 seconds / DB2 0.000332 seconds > > Actually, on second thoughts that looks a lot like DB2 is caching the > query results and is just returning the cached results when you repeat > the query. > > I'm not sure to what extent PostgreSQL is capable of result caching, but > I'd be surprised if it could do as much as DB2. > > -- > Craig Ringer > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Repeated Query is much slower in PostgreSQL8.2.4 than DB2 9.1
Hi Greg, I am doing performance test by running unit test program to compare time used on PostgreSQL and DB2. As you pointed out, there are cases that PostgreSQL is faster. Actually in real world for my application, repeatedly executing same query statement will hardly happen. I am investigating this because on the performance test report automatically generated by running unit test program, DB2 is 20-30 times faster than PostgreSQL in some test cases because of repeatedly executed query. I am thinking that ignoring these test cases for performance measure is safe and acceptable, since PostgreSQL is quicker than DB2 for the first execution. Thank you. Ning On Wed, Jul 15, 2009 at 5:37 PM, Greg Stark wrote: > On Wed, Jul 15, 2009 at 9:27 AM, Craig > Ringer wrote: >> On Wed, 2009-07-15 at 12:10 +0900, ning wrote: >> >>> First execution: PostgreSQL 0.006277 seconds / DB2 0.009028 seconds >>> Second execution: PostgreSQL 0.005932 seconds / DB2 0.000332 seconds >> >> Actually, on second thoughts that looks a lot like DB2 is caching the >> query results and is just returning the cached results when you repeat >> the query. > > > Yeah, is 6ms really a problematic response time for your system? > > If so you might consider whether executing millions of small queries > is really the best approach instead of absorbing them all into queries > which operate on more records at a time. For example, it's a lot > faster to join two large tables than look up matches for every record > one by one in separate queries. > > There's no question if you match up results from DB2 and Postgres one > to one there will be cases where DB2 is faster and hopefully cases > where Postgres is faster. It's only interesting if the differences > could cause problems, otherwise you'll be running around in circles > hunting down every difference between two fundamentally different > products. > > -- > greg > http://mit.edu/~gsstark/resume.pdf > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Repeated Query is much slower in PostgreSQL8.2.4 than DB2 9.1
(never executed) -> Sort (cost=97.16..100.66 rows=1400 width=16) (never executed) Sort Key: res.void -> Seq Scan on resolution_value res (cost=0.00..24.00 rows=1400 width=16) (never executed) -> Sort (cost=104.83..108.61 rows=1510 width=12) (never executed) Sort Key: b.void -> Seq Scan on bigint_value b (cost=0.00..25.10 rows=1510 width=12) (never executed) Total runtime: 0.479 ms (46 rows) - Best regards, Ning On Thu, Jul 16, 2009 at 7:37 AM, Mike Ivanov wrote: > ning wrote: >> >> The log is really long, > > Which usually signals a problem with the query. > >> but I compared the result of "explain analyze" >> for first and later executions, except for 3 "time=XXX" numbers, they >> are identical. >> > > They are supposed to be identical unless something is really badly broken. > >> I agree with you that PostgreSQL is doing different level of caching, >> I just wonder if there is any way to speed up PostgreSQL in this >> scenario, > > This is what EXPLAIN ANALYZE for. Could you post the results please? > > Cheers, > Mike > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Repeated Query is much slower in PostgreSQL8.2.4 than DB2 9.1
Hi Andres, The log for the test you suggested is as follows in PostgreSQL8.2.4, but I cannot find a clue to prove or prove not PostgreSQL is doing plan caching. Best regards, Ning - job=# prepare test_query as SELECT oid_,void,nameId,tag,intval,lowerbound,upperbound,crossfeeddir,feeddir,units,opqval,bigval,strval FROM (SELECT attributeOf,void,nameId,tag,intval,lowerbound,upperbound,crossfeeddir,feeddir,units,opqval,bigval,strval FROM DenormAttributePerf WHERE attributeof IN (SELECT oid_ FROM JobView WHERE JobView.JobId=100 and JobView.assignedTo_=1) AND nameId in (6)) x RIGHT OUTER JOIN (SELECT oid_ FROM JobView WHERE JobView.JobId=100 and JobView.assignedTo_=1) y ON attributeof = oid_ FOR READ ONLY ; PREPARE job=# execute test_query; oid_ | void | nameid | tag | intval | lowerbound | upperbound | crossfeeddir | feeddir | units | opqval | bigval | strval --+--++-++++--+-+---+++ 101 | || |||| | | ||| (1 row) job=# execute test_query; oid_ | void | nameid | tag | intval | lowerbound | upperbound | crossfeeddir | feeddir | units | opqval | bigval | strval --+--++-++++--+-+---+++ 101 | || |||| | | ||| (1 row) - On Thu, Jul 16, 2009 at 7:51 AM, Andres Freund wrote: > On Wednesday 15 July 2009 10:27:50 Craig Ringer wrote: >> On Wed, 2009-07-15 at 12:10 +0900, ning wrote: >> > First execution: PostgreSQL 0.006277 seconds / DB2 0.009028 seconds >> > Second execution: PostgreSQL 0.005932 seconds / DB2 0.000332 seconds >> >> Actually, on second thoughts that looks a lot like DB2 is caching the >> query results and is just returning the cached results when you repeat >> the query. > Are you sure getting the query *result* is causing the delay? If my faint > memory serves right DB2 does plan caching - PG does not. > To test this theory you could prepare it and execute it twice. > > Prepare it: > PREPARE test_query AS SELECT void,nameId,tag FROM (SELECT void,nameId,tag, > FROM Attr > WHERE attributeof IN (SELECT oid_ FROM ItemView WHERE > ItemView.ItemId=?)) x RIGHT OUTER JOIN (SELECT oid_ FROM ItemView > WHERE ItemView.ItemId=? and ItemView.assignedTo_=?) y ON attributeof = > oid_ FOR READ ONLY; > > > Execute it: > EXECUTE test_query; > EXECUTE test_query; > > Greetings, > > Andres > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Repeated Query is much slower in PostgreSQL8.2.4 than DB2 9.1
Hi Andres, By executing #explain analyze execute test_query; the first execution cost 0.389 seconds the second cost 0.285 seconds Greetings, Ning On Thu, Jul 16, 2009 at 4:45 PM, Andres Freund wrote: > On Thursday 16 July 2009 03:11:29 ning wrote: >> Hi Andres, >> >> The log for the test you suggested is as follows in PostgreSQL8.2.4, >> but I cannot find a clue to prove or prove not PostgreSQL is doing >> plan caching. > Well. How long is the PREPARE and the EXECUTEs taking? > > > Andres > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Repeated Query is much slower in PostgreSQL8.2.4 than DB2 9.1
I'm sorry, they are in milliseconds, not seconds. The time used is quite same to the result of "explain analyze select " I pasted above, which was " Total runtime: 0.479 ms". Greetings, Ning On Thu, Jul 16, 2009 at 6:33 PM, Andres Freund wrote: > On Thursday 16 July 2009 11:30:00 ning wrote: >> Hi Andres, >> >> By executing >> #explain analyze execute test_query; >> >> the first execution cost 0.389 seconds >> the second cost 0.285 seconds > Seconds or milliseconds? > > If seconds that would be by far slower than the plain SELECT, right? > > Andres > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
