"Phil Endecott" <spam_from_postgresql_general@chezphil.org> 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