Re: Query running longer

On Fri, 2024-02-02 at 02:27 +0530, veem v wrote:
> We have the below query which is running for ~45 seconds on postgres aurora reader instance.
> I have captured the explain analyze. Want to understand, where exactly the resources are
> getting spent and if we can be able to optimize it further.

Aurora <> PostgreSQL, but here is what I can see:

- The index scan on SCHEMA1."TAB2" has to check 2 million extra
  rows because "work_mem" is too small.  Almost the complete time
  is spent there.

- You may be getting a bad plan, because the statistics on
  SCHEMA1.TAB4 are either out of date or not detailed enough,
  which makes PostgreSQL underestimate the result size.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com