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