Re: avoiding seq scan without duplicating - Mailing list pgsql-general

"Andrus" <kobruleht2@hot.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