Re: Help with query optimizer - Mailing list pgsql-general

From Laurenz Albe
Subject Re: Help with query optimizer
Date
Msg-id 3d4bf841b153bb63910ed2835dc3683e6129c422.camel@cybertec.at
Whole thread Raw
In response to Re: Help with query optimizer  (David Mullineux <dmullx@gmail.com>)
List pgsql-general
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 define 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 they 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 : conversations 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



pgsql-general by date:

Previous
From: David Mullineux
Date:
Subject: Re: Help with query optimizer
Next
From: Greg Sabino Mullane
Date:
Subject: Re: Suggestion for memory parameters