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

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(at)archonet(dot)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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Helio Campos Mello de Andrade