On Fri, Mar 16, 2018 at 3:36 PM, Amit Kapila <amit.kapil...@gmail.com> wrote: > On Wed, Mar 14, 2018 at 12:01 AM, Robert Haas <robertmh...@gmail.com> wrote: >> >> 0003 introduces a new upper relation to represent the result of >> applying the scan/join target to the topmost scan/join relation. I'll >> explain further down why this seems to be needed. Since each path >> must belong to only one relation, this involves using >> create_projection_path() for the non-partial pathlist as we already do >> for the partial pathlist, rather than apply_projection_to_path(). >> This is probably marginally more expensive but I'm hoping it doesn't >> matter. (However, I have not tested.) >> > > I think in the patch series this is the questionable patch wherein it > will always add an additional projection path (whether or not it is > required) to all Paths (partial and non-partial) for the scanjoin rel > and then later remove it (if not required) in create_projection_plan. > As you are saying, I also think it might not matter much in the grand > scheme of things and if required we can test it as well, >
I have done some tests to see the impact of this patch on planning time. I took some simple statements and tried to compute the time they took in planning. Test-1 ---------- DO $$ DECLARE count integer; BEGIN For count In 1..1000000 Loop Execute 'explain Select ten from tenk1'; END LOOP; END; $$; In the above block, I am explaining the simple statement which will have just one path, so there will be one additional path projection and removal cycle for this statement. I have just executed the above block in psql by having \timing option 'on' and the average timing for ten runs on HEAD is 21292.388 ms, with patches (0001.* ~ 0003) is 22405.2466 ms and with patches (0001.* ~ 0005.*) is 22537.1362. These results indicate that there is approximately 5~6% of the increase in planning time. Test-2 ---------- DO $$ DECLARE count integer; BEGIN For count In 1..1000000 Loop Execute 'explain select hundred,ten from tenk1 order by hundred'; END LOOP; END; $$; In the above block, I am explaining the statement which will have two paths, so there will be two additional path projections and one removal cycle for one of the selected paths for this statement. The average timing for ten runs on HEAD is 32869.8343 ms, with patches (0001.* ~ 0003) is 34068.0608 ms and with patches (0001.* ~ 0005.*) is 34097.4899 ms. These results indicate that there is approximately 3~4% of the increase in optimizer time. Now, ideally, this test should have shown more impact as we are adding additional projection path for two paths, but I think as the overall time for planning is higher, the impact of additional work is not much visible. I have done these tests on the Centos VM, so there is some variation in test results. Please find attached the detailed results of all the tests. I have not changed any configuration for these tests. I think before reaching any conclusion, it would be better if someone repeats these tests and see if they also have a similar observation. The reason for doing the tests separately for first three patches (0001.* ~ 0003.*) is to see the impact of changes without any change related to parallelism. -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Test-1 --------- DO $$ DECLARE count integer; BEGIN For count In 1..1000000 Loop Execute 'explain Select ten from tenk1'; END LOOP; END; $$; Head -------- Time: 21228.829 ms (00:21.229) Time: 21353.244 ms (00:21.353) Time: 21758.958 ms (00:21.759) Time: 20999.708 ms (00:21.000) Time: 21804.451 ms (00:21.804) Time: 20851.715 ms (00:20.852) Time: 21514.198 ms (00:21.514) Time: 20677.121 ms (00:20.677) Time: 21685.576 ms (00:21.686) Time: 21050.080 ms (00:21.050) Patches (upto 0003) -------------------------- Time: 23425.923 ms (00:23.426) Time: 21609.624 ms (00:21.610) Time: 21865.652 ms (00:21.866) Time: 22006.988 ms (00:22.007) Time: 22427.953 ms (00:22.428) Time: 23195.211 ms (00:23.195) Time: 22692.508 ms (00:22.693) Time: 21757.227 ms (00:21.757) Time: 22574.941 ms (00:22.575) Time: 22496.439 ms (00:22.496) Time: 21648.699 ms (00:21.649) Patches (upto 0005) --------------------------- Time: 23003.406 ms (00:23.003) Time: 22624.973 ms (00:22.625) Time: 22426.394 ms (00:22.426) Time: 22700.590 ms (00:22.701) Time: 22452.472 ms (00:22.452) Time: 23048.367 ms (00:23.048) Time: 21845.491 ms (00:21.845) Time: 22832.643 ms (00:22.833) Time: 21995.769 ms (00:21.996) Time: 22441.257 ms (00:22.441) Test-2 ------- DO $$ DECLARE count integer; BEGIN For count In 1..1000000 Loop Execute 'explain select hundred,ten from tenk1 order by hundred'; END LOOP; END; $$; Head ----- Time: 32229.360 ms (00:32.229) Time: 33310.348 ms (00:33.310) Time: 32712.002 ms (00:32.712) Time: 32630.473 ms (00:32.630) Time: 33097.111 ms (00:33.097) Time: 32487.799 ms (00:32.488) Time: 33195.718 ms (00:33.196) Time: 33166.606 ms (00:33.167) Time: 32533.331 ms (00:32.533) Time: 33335.595 ms (00:33.336) Patches (upto 0003) ------------------ Time: 34715.029 ms (00:34.715) Time: 34711.412 ms (00:34.711) Time: 33489.049 ms (00:33.489) Time: 34486.523 ms (00:34.487) Time: 33946.198 ms (00:33.946) Time: 33630.632 ms (00:33.631) Time: 33547.556 ms (00:33.548) Time: 34417.672 ms (00:34.418) Time: 33921.339 ms (00:33.921) Time: 33815.198 ms (00:33.815) Patch (upto 0005) ------------------ Time: 33686.723 ms (00:33.687) Time: 34701.752 ms (00:34.702) Time: 33747.216 ms (00:33.747) Time: 34400.340 ms (00:34.400) Time: 34378.550 ms (00:34.379) Time: 34855.390 ms (00:34.855) Time: 33538.599 ms (00:33.539) Time: 34292.776 ms (00:34.293) Time: 34062.697 ms (00:34.063) Time: 33310.856 ms (00:33.311)
