Re: Slow SQL?

hamann.w@t-online.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