Re: Very slow queries w/ NOT IN preparation (seems like a bug, test case)

Sergey Konoplev wrote:
>
>> Finally - did you compile this from source yourself, or is it installed
>> via apt? I'm wondering whether you have an unusual version of a library
>> linked in, and it's taking a long time to parse the query.
>
> I've compiled it from sources. BTW, I tested it on both 8.3.3 and 8.3.4.

Hmm - nothing unusual about your setup, I suppose?

>> Actually, we can test that. If you run the same query against an empty
>> table, does it take more than a few milliseconds?
>
> Yes it does. Nothing has changed.

So it *must* be something in the planner/parser/explain code, and
something specific to your setup.

If you connect via psql and then (as root, in another terminal) do:
ps auxw | grep postgres
you should see the backend that corresponds to your psql connection.
strace -p <pid>
should then show system calls as they are executed (assuming you have it
installed). Execute the explain, and see what is output.

Mine flies past, but is composed almost entirely of "gettimeofday" calls
(10,000 of them) apart from at the very end where we get some write and
send/recv calls (to print the explain results). I've heard of some
people having slow "gettimeofday" calls, but not on linux. On the other
hand, that seems to be the main difference between strace output with
"not in" compared to "in".

--
Richard Huxton
Archonet Ltd