On Wed, Feb 25, 2009 at 12:38 AM, Lawrence, Ramon <ramon.lawre...@ubc.ca> wrote: >> -----Original Message----- >> From: Robert Haas >> Sadly, there seem to be a number of cases in the Z7 database where the >> optimization makes things significantly worse (specifically, queries >> 2, 3, and 7, but especially query 3). Have you investigated what is >> going on there? I had thought that we had sufficient safeguards in >> place to prevent this optimization from kicking in in cases where it >> doesn't help, but it seems not. There will certainly be real-world >> databases that are more like Z7 than Z1. > > I agree that there should be no noticeable performance difference when > the optimization is not used (single batch case or no skew). I think > the patch achieves this. The optimization is not used in those cases, > but we will review to see if it is the code that by-passes the > optimization that is causing a difference.
Yeah we need to understand what's going on there. > The query #3 timing difference is primarily due to a flaw in the > experimental setup. For some reason, query #3 got executed before #4 > with the optimization on, and executed after #4 with the optimization > off. This skewed the results for all runs (due to buffering issues), > but is especially noticeable for Z7. Note how query #4 is always faster > for the optimization on version even though the optimization is not > actually used for those queries (because they were one batch). I expect > that if you run query #3 on Z7 in isolation then the results should be > basically identical. > > I have attached the SQL script that Joshua sent me. The raw data I have > posted at: http://people.ok.ubc.ca/rlawrenc/test.output I don't think we're really doing this the right way. EXPLAIN ANALYZE has a measurable effect on the results, and we probably ought to stop the database and drop the VM caches after each query. Are the Z1-Z7 datasets on line someplace? I might be able to rig up a script here. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
