Message-ID: <3d4bf841b153bb63910ed2835dc3683e6129c422.camel@cybertec.at>
Subject: Re: Help with query optimizer
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: David Mullineux <dmullx@gmail.com>, Siraj G <tosiraj.g@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Date: Tue, 01 Oct 2024 12:39:02 +0200
In-Reply-To: <CAGsyd8V9XOBpOOrg+jvmJ4L56ATyj8kZMZ2w4-9T-CVMWBcPTw@mail.gmail.com>
References: 
	<CAC5iy60_cx-d=0eXNgzX9bmZepFaZ3isqyX=D=UpxUcOgW+9cg@mail.gmail.com>
	 <CAGsyd8V9XOBpOOrg+jvmJ4L56ATyj8kZMZ2w4-9T-CVMWBcPTw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/3d4bf841b153bb63910ed2835dc3683e6129c422.camel%40cybertec.at>
Precedence: bulk

On Tue, 2024-10-01 at 09:36 +0100, David Mullineux wrote:
> > On Tue, 1 Oct 2024, 08:53 Siraj G, <tosiraj.g@gmail.com> wrote:
> > Can you please help with the attached query as it is taking time to
> > execute. The execution plan for the same is below:
> >
> > [EXPLAIN (ANALYZE, BUFFERS) output]
>
> Its usually helpful to give some details on what 'slow' means and then de=
fine what
> 'fast enough ' means. Just so we know when to stop optimizing.
> You should also help by giving the DDL of those.tables and any indexes th=
ey have etc..

Table definitions and the actual query text would be most helpful, yes.

The PostgreSQL version is essential too.

> The plan shows me that there are expensive seq scans on 2 tables : conver=
sations and tasks.

Those sequential scans are quite cheap; there is nothing to optimize.
The time is spent in two index scans that are repeated a lot, because they
are on the inner side of a nested loop:

- the index scan on "messages" is repeated 53708 times
- the index scan on "mailMessages" is repeated 173750 times


Yours,
Laurenz Albe