"Andrus" <kobruleht2(at)hot(dot)ee> writes:
> Simple query is slow, performs seq scan while index exists:
> explain select count(*)::integer as cnt
> from firma2.dok
> where dokumnr in (888817,2) and
> dokumnr not in (select dokumnr FROM firma2.bilkaib WHERE
> alusdok='LF' -- and dokumnr in (888817,2)
> )
> Index is used if join condition is duplicated in subquery:
> explain select count(*)::integer as cnt
> from firma2.dok
> where dokumnr in (888817,2) and
> dokumnr not in (select dokumnr FROM firma2.bilkaib WHERE
> alusdok='LF' and dokumnr in (888817,2)
> )
The proposed transformation is not correct because of the odd behavior
of NOT IN with respect to nulls.
regards, tom lane