0

I had a very weird problem and I really cant realise what I'm doing wrong. I got a new project to fix and previous developer told me that some functions stopped working after Postgresql update. Using trial and error mehtod I discovered that page stops (?) loading when running extacly this piece of code. To be fair it's not even STOPPING absolutely, page loads and loads infinitely. This is the query code:

SELECT DISTINCT ON (z."Zlecenie") z."Zlecenie",
    sz."Nazwa",
    erp."Odbiorca",
    erp."ZleconePrzez",
    erp."OpisWysylki",
    z."DataWplywu",
    to_char(z."PlanowaneZakonczenie", 'YYYY-MM-DD'::text) AS "ZaplanowanaDataZlecenia",
    to_char(z."PlanowaneZakonczenie", 'HH24:MI'::text) AS "ZaplanowanyCzasZlecenia",
    z."PlanowaneZakonczenie",

    ( SELECT min("ZleceniaERP"."StatusZlecenia") AS min
          FROM "ZleceniaERP"
          WHERE "ZleceniaERP"."Zlecenie"::text = z."Zlecenie"::text) AS "StatusZlecenia",

    ( SELECT "SprawdzCzyZlecenieMaTowaryNieWidniejaceWMagazynie" (z."Zlecenie"::text) AS "SprawdzCzyZlecenieMaTowaryNieWidniejaceWMagazynie") AS "CzyTowarySaWMagazynie",

    ( SELECT sum("ZleceniaERP"."Zamowiono") AS sum
          FROM "ZleceniaERP"
          WHERE "ZleceniaERP"."Zlecenie"::text = z."Zlecenie"::text) AS "IloscPar",

    ( SELECT round(wg."WAGA", 1) AS "WAGA"
          FROM "WagaZlecenia_view" wg
          WHERE wg."Zlecenie"::text = z."Zlecenie"::text) AS "WAGA",

    ( SELECT sum("ZleceniaERP"."Zrealizowano") AS ilosczre
          FROM "ZleceniaERP"
          WHERE "ZleceniaERP"."Zlecenie"::text = z."Zlecenie"::text) AS ilosczre,

    ( SELECT round(wg."OBJETOSC", 1) AS "OBJETOSC"
           FROM "WagaZlecenia_view" wg
           WHERE wg."Zlecenie"::text = z."Zlecenie"::text) AS "OBJETOSC",

    to_char(z."DataFaktury", 'YYYY-MM-DD'::text) AS "DataFaktury",
    z."NumerFaktury"
FROM "ZleceniaERP" z,
    "Towary" t,
    "StatusyZlecenia" sz,
    "ERPNaglowki" erp
WHERE sz."ID" = z."StatusZlecenia" AND z."Towar"::text = t."Barkod"::text AND 
z."NieWyswietlajWKolejce" = false AND erp."Zlecenie"::text = z."Zlecenie"::text;

And here is the 2 queries from query above that interrupts loading:

( SELECT round(wg."WAGA", 1) AS "WAGA"
      FROM "WagaZlecenia_view" wg
      WHERE wg."Zlecenie"::text = z."Zlecenie"::text) AS "WAGA",

and this:

( SELECT round(wg."OBJETOSC", 1) AS "OBJETOSC"
       FROM "WagaZlecenia_view" wg
      WHERE wg."Zlecenie"::text = z."Zlecenie"::text) AS "OBJETOSC",

Here is the WagaZlecenia_view creation code, this query works ok btw.

CREATE OR REPLACE VIEW public."WagaZlecenia_view" AS
    SELECT z."Zlecenie",
      sum(z."Zamowiono"::double precision * pt."WagaJednostkowaERP")::numeric AS "WAGA",
      sum(z."Zamowiono"::double precision * pt."ObjetoscERP" / pt."OpakowanieIloscERP")::numeric AS "OBJETOSC"
    FROM "ZleceniaERP" z,
      "Porownanie_Trawers_view" pt
    WHERE z."Towar"::text = pt."Towar"::text AND z."Zamowiono" <> 0
    GROUP BY z."Zlecenie"
    ORDER BY z."Zlecenie";
ALTER TABLE public."WagaZlecenia_view"
OWNER TO postgres;

I don't really know what could happened here. Now I'm running PostgreSQL 9.3.24, unfortunately i dont know what was the version before update. Thanks in advance for every response!

MateoSkyline
  • 309
  • 1
  • 5
  • 17
  • 1
    Unrelated, but: upgrading to 9.3 today seems a bit pointless given that it's going to be end-of-live next month. Why didn't you upgrade to 10 or at least 9.6? –  Oct 24 '18 at 06:39
  • 2
    A very common reason for slow performing queries after an upgrade are missing statistics. You should run `analyze` on all your tables. See also [here](https://stackoverflow.com/questions/52958570/what-is-the-fastest-way-to-rebuild-postgresql-statistics-from-zero-scratch-with) –  Oct 24 '18 at 06:40
  • 1
    Look at the `EXPLAIN (ANALYZE, BUFFERS)` output for the queries. – Laurenz Albe Oct 24 '18 at 07:00
  • I did analyze and it worked (?) but it still loaded for 8 minutes (for 93 rows). I dont know if it matters but I forgot to tell that this is a view. It changes something? As I mentioned in post, it worked before upgrade and stopped working after it. – MateoSkyline Oct 24 '18 at 11:47

0 Answers0