"Kevin Grittner" <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> wrote: >> You didn't show us the explain analyze results,
> The below is cut & paste directly from a psql run without editing. OK, so the two plans do indeed have much different node execution counts. The EXPLAIN ANALYZE instrumentation overhead is basically proportional to (rows+1)*loops summed over all the nodes in the plan, so I count about 102112 node executions in the NOT IN plan versus 1145 in the NOT EXISTS plan --- in other words, 100x more overhead for the former. > The run time of the NOT IN query, as measured by elapsed time between > SELECT CURRENT_TIMESTAMP executions, increased by 31 ms. Works out to about 30 microsec per node execution, which seems a bit high for modern machines ... and the coarse quantization of the CURRENT_TIMESTAMP results is odd too. What platform is this on exactly? > That leaves an unaccounted difference between the time > reported by EXPLAIN ANALYZE and the timestamp elapsed time of (on > average) 9 ms for the NOT IN form of the query, and 41 ms for the NOT > EXISTS for of the query. (In the run shown above, it's higher.) I'm > guessing that this is the time spent in parsing and planning the query. Parse/plan time is one component, and another is the time spent by EXPLAIN preparing its output display, which is not an area we've spent any time at all optimizing --- I wouldn't be surprised if it's kinda slow. However, these plans are relatively similar in terms of the complexity of the display, so it is odd that there'd be so much difference. > What is the best way to see where this time is going? Profiling with gprof or some such tool might be educational. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq