On Tue, 12 Jul 2016 09:12:23 +0000 Albe Laurenz <laurenz.a...@wien.gv.at> wrote:
> haman...@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 > 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
