"Phil Endecott" <spam_from_postgresql_general@xxxxxxxxxxxx> writes: > I was not patient enough to wait for the remaining explain-analyse results, > but I feel that there is a linear slowdown of about 60x between the raw > query and the explain-analyse version. Slow gettimeofday() ... fairly common on desktop-grade PC hardware :-(. You seem to have a particularly bad case of it, though, as extrapolating from your numbers suggests the overhead is something like 20 microseconds per clock reading; the other reporters we've heard from seemed to get around 1 to 5 usec IIRC. A lot of PCs still use clock chips that were designed back when multiple microseconds to read the clock wasn't unreasonable, but with CPU speeds in the GHz range this is just sucky hardware. It shows up on this example because most of the node entry/exits are for the Materialize node, which can return the next row from its internal array in about no time flat, so the clock readings represent huge percentage overhead. > But the peculiar behaviour of explain-analyse is really a distraction from > the fact that the query is slow, especially when the limit value is large. You need a "hashed subplan" for NOT IN to work reasonably fast. The fact you're not getting one suggests you either have to raise work_mem, or you're using some weird datatype that doesn't support hashing. regards, tom lane
