Re: Postgresql 14 performance

Hello Wim,

Thank you for your quick response.
Yes, it is not a regular table, it is a view, and yes 1506074 IS NULL is
always false. ( the generated query.)

I don't think it is about the system workload. Because the system has a
very low load on the weekend.

While the query took an average of 1 second, it suddenly took 5-6 seconds
according to the PostgreSQL log file. I couldn't find the reason for this
slowdown.

Warm regards,
Kenn

Wim Bertels <wim(dot)bertels(at)ucll(dot)be>, 21 Ağu 2022 Paz, 20:06 tarihinde şunu
yazdı:

> Hello Kenny,
>
> have you tried to correlate with the system load for example?
>
> also, it is normal to have variations,
> although big variations under the same circumstances might indicate
> another problem on the system (somewhere in your hw/sw stack)
>
> --
>
> a quick look at the query + plan:
>
> ..WHERE "Extent1"."DdoId" = 1506074 OR "Extent1"."DdoId" IS NULL AND
> 1506074 IS NULL LIMIT 1;
>
> 1506074 IS NULL is always false (generated query?)
>
> (never executed lines, ERP_WORK_ORDER_DDO_INFO does not seem to a
> regular table, hence the query plan, (there a few highlighting
> tools/sites for the query plan you could also check))
>
> mvg,
> Wim
>
> Kenny Bachman schreef op zo 21-08-2022 om 19:05 [+0300]:
> > Hi Team,
> >
> > Have a lovely Sunday.
> > I have a problem with PostgreSQL performance. I am using PostgreSQL
> > 14.5 on Linux physical server. My query was taking 1 or 2 seconds
> > yesterday, but today it is taking 5-6 seconds. Also, nothing has
> > changed in the database. Also, when I run the query for the second
> > time, the query runs for the same time, so I expect it to be more
> > performant the second time because of the cache.
> > (the database cache hit ratio is 97,8)
> >
> > Lastly, some other queries take milliseconds normally, but sometimes
> > they take minutes. I didn't understand why this is happening. (There
> > is no lock, no table bloating and up to date analyze)
> >
> >
> > I am adding the explain analyze output as an attachment.
>
>