Evergreen

Bug #1400376
Comment #7

Comment 7 for bug 1400376

Revision history for this message

Mike Rylander (mrylander) wrote on 2014-12-11:

Thomas,

Sorry, but this is entirely unconvincing. You have shown exactly one run of one query (for each variant) for a single record. Your timings are worse than useless because they confuse the matter by being presented as evidence of a pattern, but they are not.

Before you start complaining about my use of Ben's timings, read my first sentence again: "assuming they are stable."

[Aside the first: In order to help future timing testers (you included), I need to point out that you're also reporting the wrong part of the output if you want to use E-A as evidence (and, in fairness, so did Ben, and I should have pointed it out before). The number you want is the ending time of the outermost part of the EXPLAIN output itself, not the post-result time, as that includes variable network latency as well (yes, even over loopback, and even over a unix socket). In that case you'll see that my solution has /exactly/ the same timing as yours, and retains the advantages I describe above. But, that fact shows the problem in your test -- the first one primed the cache for the rest, at the cost of seeming slower. ]

With that in mind, though, let's look at Ben's timing again, just for the sake of argument (it's still not statistically valid...): 0.025 vs 0.058 -- it's actually /worse/ than double. And, the first run was faster, or so the paste would make it seem.

Even so, and leaving the timing done so far out or not, the main point I raise stands: LEFT JOIN will allow for better plans (and faster execution of nearly identical plans) than INNER JOIN. If you'd tested the situations I described and found them to be faster over a statistically meaningful set of repeated runs, I would be happily convinced.

Here's some further reading as to my reasoning: http://rhaas.blogspot.com/2010/06/why-join-removal-is-cool.html (note, INNER JOIN removal, mentioned at the end, is not in PG yet, but it's being worked on for 9.5). A google for "postgres join removal" will turn up more.

[Aside the second: Finally, the solutions we respectively propose are mutually exclusive. There is no point in using mine with an INNER join on the underlying view, and there is no point in yours with the dependent view filtering null attr column values. To save everyone the trouble, testing both together can be skipped. ]

Thanks.

Thomas,

Sorry, but this is entirely unconvincing.  You have shown exactly one run of one query (for each variant) for a single record.  Your timings are worse than useless because they confuse the matter by being presented as evidence of a pattern, but they are not.

Before you start complaining about my use of Ben's timings, read my first sentence again: "assuming they are stable."

[Aside the first:  In order to help future timing testers (you included), I need to point out that you're also reporting the wrong part of the output if you want to use E-A as evidence (and, in fairness, so did Ben, and I should have pointed it out before).  The number you want is the ending time of the outermost part of the EXPLAIN output itself, not the post-result time, as that includes variable network latency as well (yes, even over loopback, and even over a unix socket).  In that case you'll see that my solution has /exactly/ the same timing as yours, and retains the advantages I describe above.  But, that fact shows the problem in your test -- the first one primed the cache for the rest, at the cost of seeming slower. ]

With that in mind, though, let's look at Ben's timing again, just for the sake of argument (it's still not statistically valid...): 0.025 vs 0.058 -- it's actually /worse/ than double.  And, the first run was faster, or so the paste would make it seem.

Even so, and leaving the timing done so far out or not, the main point I raise stands: LEFT JOIN will allow for better plans (and faster execution of nearly identical plans) than INNER JOIN.  If you'd tested the situations I described and found them to be faster over a statistically meaningful set of repeated runs, I would be happily convinced.

Here's some further reading as to my reasoning: http://rhaas.blogspot.com/2010/06/why-join-removal-is-cool.html (note, INNER JOIN removal, mentioned at the end, is not in PG yet, but it's being worked on for 9.5).  A google for "postgres join removal" will turn up more.

[Aside the second: Finally, the solutions we respectively propose are mutually exclusive.  There is no point in using mine with an INNER join on the underlying view, and there is no point in yours with the dependent view filtering null attr column values. To save everyone the trouble, testing both together can be skipped. ]

Thanks.