Re: Slow SQL?

On Tue, 12 Jul 2016 09:12:23 +0000
Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

> 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
>
Thx for your suggestions. Tried to use NOT EXISTS and the query was about half a second quicker so not much difference...
But when I try to run the 3 queries separately, then they are very quick, 2 barely measurable and the third takes about 1,5 seconds. The union query
takes a little over 9 seconds, so I guess the union part is the bottleneck?

BTJ