Re: Slow SQL?

hamann(dot)w(at)t-online(dot)de wrote:
Bjørn T Johansen wrote:
>> I am trying to move a small system from Oracle to PostgreSQL and I have come upon a sql that runs
>> really slow compared to on the Oracle database and I am not able to interpret why this is slow.

> I have experienced that some subqueries can be quite slow, and would suspect the NOT IN
> clause. I occasionally rewrite
> NOT IN (select key from unwanted_candidates)
> as
> IN (select key from possible_candidates except select key from unwanted_candidates)

I would try to rewrite these clauses to NOT EXISTS, for example

a.avlsnr Not In (Select avlsnr From dyr_pause_mot)

could be

NOT EXISTS (SELECT NULL FROM dyr_pause_mot WHERE avlsnr = a.avlsnr)

This can be executed as anti-join and is often more efficient.

Yours,
Laurenz Albe