Hi Sergey,
- It's just guess but it could be the range of a SERIAL TYPE that is generating this behavior.
An example is:
Knowing that "table1_id" is primary a key ( the table will be ordered by it ) and that a serial range is 2147483647 long.
(a) you use 2000 different numbers of this range the planner will search for the 2000 numbers in yours 5000 rows in an ordered way and it will stop when the searched number can't be found any more. It will make, in the worst case, 2000*log(5000) tests ( aprox: 24.575 ).
(b) If you use "~2000" different numbers and the system understands that you want that the rest of the range ( 2147481647 numbers ) have to be searched and it will cost 263.876.368.186 tests.
On Tue, Nov 11, 2008 at 12:20 PM, Richard Huxton
<dev@archonet.com> wrote:
Sergey Konoplev wrote:
>
> Another thing is that even I set statement_timeout to 20s the query
> with NOT IN finishes working after 30+ seconds without "canceled by
> statement timeout" error.
Maybe it's not taking that long to execute the query then.
Maybe something to do with process startup is delaying things - could
you tweak the test script to send the outputs of the explain somewhere
other than /dev/null? That way we'd know if there was a big difference
between query-execution-time and process-execution-time.
--
Richard Huxton
Archonet Ltd
--
Helio Campos Mello de Andrade