3

Through this strategy, time consumed by queries for my application have dropped to extreme lows; however, some queries consume more milliseconds than necessary.

The reason why is that upon examination of EXPLAIN ANALYZE, I found that sub-statements that depend upon the RETURNING of another query will still execute despite the source query returning an empty set.

In my application, I've reduced the number of queries from many 10s to four. All are bulk operations. The ones that have many sub-statements are the ones that consume 5ms or more. It's those I'd like to trim.

I gave functions another chance and tried to manually prevent chain sections by using IF statements. I had to use TEMPORARY TABLEs to store the RETURNING results and check them in the IFs.

I can't explain it, but the time consumed was 2 to 4x that consumed by the equivalent CTE.

To that end, is it possible to completely prevent a CTE sub-statement from executing if a RETURNING that it depends upon has no rows?

Another 50% reduction

I long ago followed the advice here, stripped out my comma joins where I could and replaced them with EXISTS.

Due to my inexperience, I replaced an EXISTS with a comma join in one sub-statement because I thought one query needed it instead. The average time consumed dropped again.

I changed all of the EXISTS back to comma joins, and for queries with empty RETURNINGs, the time was chopped in half or more.

In those cases, the EXISTS go to disk, and it appears as if they still execute despite a previous WHERE condition that would guarantee no writing.

Performance for when the previous RETURNINGs return are the same. The resulting relationships are 1:1, and the tables referenced are collections of integer references to other tables' primary keys. The estimated EXPLAIN ANALYZE costs have gone through the roof.

  • 1
    Interesting question. I suspect the answer is "no" because at plan-time we don't know that the other CTE term's RETURNING clause won't return rows. However, if it's affecting no rows shouldn't it do effectively nothing and return quickly? What's the CTE term that's so slow even with zero rows of input? – Craig Ringer Dec 29 '14 at 6:58
  • @CraigRinger When I split the queries up, they all execute in 0.25ms. For any combination that's possible, the average time remains 0.25ms, regardless of whether the RETURNINGs are empty. If this helps, the final writing queries that depend upon the RETURNINGs still report back joins etc in EXPLAIN ANALYZE before reporting the check for EXISTS of the RETURNINGs. For final writes in the chains, I'm going to test if using a CASE can short circuit. – Jim Bob Dec 29 '14 at 7:23
  • Sure, but what's the rowcount reported on those joins etc? If they're affecting zero rows, they don't matter. 0.25ms isn't exactly slow... if you're on Windows, that's actually well below the timing accuracy your platform gives you. You should really be timing with many iterations if you're doing something that fast. – Craig Ringer Dec 29 '14 at 8:14
  • If you provide the EXPLAIN output of both variants, it should be possible to see what is happening – Thomas Kejser Dec 29 '14 at 8:43
  • @CraigRinger I'm using boost's microsec_clock on mint. From my edit, could it be that EXISTS fire despite a WHERE condition guaranteeing no writing? – Jim Bob Dec 29 '14 at 22:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.