I have some odd results from timing two versions of an update query, and
was hoping to get a better handle on how to interpret this. The query
does an update of one table. One version does three NOT IN tests
against three related tables. The other version does the logically
equivalent NOT EXISTS tests. Here are the timings:
(a) NOT IN EXPLAIN ANALYZE reports: 150 ms.
(b) Difference between SELECT CURRENT_TIMESTAMP values before and
after the NOT IN EXPLAIN ANALYZE: 171 ms.
(c) Difference between SELECT CURRENT_TIMESTAMP values before and
after NOT IN query without EXPLAIN ANALYZE: 140 ms.
(d) NOT EXISTS EXPLAIN ANALYZE reports: 9 ms.
(e) Difference between SELECT CURRENT_TIMESTAMP values before and
after the NOT EXISTS EXPLAIN ANALYZE: 62 ms.
(f) Difference between SELECT CURRENT_TIMESTAMP values before and
after NOT EXISTS query without EXPLAIN ANALYZE: 62 ms.
I repeated these tests many times. After each test I updated the table
back to its original state and ran CLUSTER and VACUUM ANALYZE, then
allowed a minute of settling time. The timings are remarkably
consistent from one try to the next with an occasional outlier on the
high side.
It seems to me that these results indicate that EXPLAIN ANALYZE
distorts the plan for the IN query (b - c = 31 ms), but not the plan for
the EXISTS query (e == f). The time to bracket the UPDATE with the
SELECT CURRENT_TIMESTAMP queries can't exceed 21 ms (b - a). That seems
like a long time, though, so I suspect that some of that time is going
to the same place that most of the 53 ms (e - d) goes on the NOT EXISTS
query. Where would that be? Parse and plan phases? Is there a way to
pin that down better?
I was planning on posting the queries with plans and timings to
illustrate a costing issue, but when I saw these timings I figured I'd
ask about this first. It raises what seems like a more fundamental
question, which would have an impact on collecting all of the
interesting information. If execution time is 9 ms, but there is
something else adding several times that, perhaps that something else is
interesting in itself.
Thanks for any info.
-Kevin