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

"Sergey Konoplev" <gray(dot)ru(at)gmail(dot)com> writes:
> I've faced strange parser (or may be planner) behaviour. When I do
> "EXPLAIN SELECT 1 FROM table1 WHERE table1_id IN (...~2000 ids
> here...);" it works as fast as I expect (50 ms). But when I rewrite it
> using NOT IN "EXPLAIN SELECT 1 FROM table1 WHERE table1_id NOT IN
> (...~2000 ids here...);" it gets much more slower (34537 ms).

FWIW, I can't reproduce your problem here. The output from your
script looks like this:

------------------------------------------
Testing EXPLAIN w/ NOT IN...
------------------------------------------
100 ids: 16 ms
200 ids: 17 ms
300 ids: 17 ms
400 ids: 18 ms
500 ids: 19 ms
600 ids: 20 ms
700 ids: 21 ms
800 ids: 22 ms
900 ids: 23 ms
1000 ids: 24 ms
1100 ids: 25 ms
1200 ids: 25 ms
1300 ids: 26 ms
1400 ids: 28 ms
1500 ids: 28 ms
1600 ids: 29 ms
1700 ids: 30 ms
1800 ids: 31 ms
1900 ids: 32 ms
2000 ids: 33 ms

------------------------------------------
Testing EXPLAIN w/ IN...
------------------------------------------
100 ids: 16 ms
200 ids: 17 ms
300 ids: 18 ms
400 ids: 19 ms
500 ids: 20 ms
600 ids: 21 ms
700 ids: 22 ms
800 ids: 23 ms
900 ids: 24 ms
1000 ids: 25 ms
1100 ids: 26 ms
1200 ids: 27 ms
1300 ids: 29 ms
1400 ids: 29 ms
1500 ids: 31 ms
1600 ids: 32 ms
1700 ids: 32 ms
1800 ids: 34 ms
1900 ids: 35 ms
2000 ids: 36 ms

regards, tom lane