On Tue, 12 Jul 2016 14:34:50 +0200 hubert depesz lubaczewski <depesz@xxxxxxxxxx> wrote: > On Tue, Jul 12, 2016 at 10:23:24AM +0200, 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 loaded your explain analyze to https://explain.depesz.com/, as: > https://explain.depesz.com/s/iXK > > as you can see there, the problem is that you made 280 thousand checks > for "sed_uttak y", which seems to be related to this part: > > > > Select a.status, a.plass, a.navn, a.avlsnr, > > date_part('day',(now() - s.dato)) dato_diff, v.tekst, COALESCE(a.avlsverdi,0) > > From sed_uttak s, sem_avlsverdi a, semin_vare v > > where a.aktiv = 1 > > And s.dato = (Select Max(y.dato) > > From sed_uttak y > > Where y.avlsnr = s.avlsnr) > > from what I understand, you're doing it to work on newest record from > sed_uttak, for each avlsnr. > > What is rowcount in the table, and how many different avlsnr are there? > > You might want to do something like: > > with s as ( > select distinct on (avlsnr) * > from sed_uttak > order by avlsnr desc, dato desc > ) > > and then use "s" instead of set_uttak, and get rid of the s.dato > = (select max....) checks. > > Best regards, > > depesz > Rewrote using the with s as method and now it's faster than the oracle querytime, so I am happy... And I learned something new, never used the with s as way before... Thx! :) BTJ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
