Postgres Pro
Downloads
Home   >   mailing lists

Re: Array of integer indexed nested-loop semi join - Mailing list pgsql-performance

From Mickael van der Beek
Subject Re: Array of integer indexed nested-loop semi join
Date
Msg-id CACM-Oyc5q29mhOv=MQbP5OM_KpF7HC36d_WUXcikihbRWUP46g@mail.gmail.com
Whole thread Raw
In response to Re: Array of integer indexed nested-loop semi join  (Jeff Janes <jeff.janes@gmail.com>)
Responses Re: Array of integer indexed nested-loop semi join  (Mickael van der Beek <mickael.van.der.beek@gmail.com>)
List pgsql-performance
Tree view
Hello Jeff,

I have waited a few hours without the query ever finishing which is the reason I said "never finishes".
Especially because the INNER JOIN version finishes within a few minutes while being combinatorial and less efficient.
The query probably only does sequential scans.

You will find the query plan using EXPLAIN here:

Thanks for your help,

Mickael

On Wed, Apr 27, 2022 at 4:28 PM Jeff Janes <jeff.janes@gmail.com> wrote:
On Wed, Apr 27, 2022 at 8:19 AM Mickael van der Beek <mickael.van.der.beek@gmail.com> wrote:

The last query does not finish after waiting for more than 15 minutes.
(The temporary view creation is very fast and required due to the same query in a CTE greatly reducing performance (by more than 5 min.) due to the optimisation barrier I'm guessing.)

How much over 15 minutes?  20 minutes doesn't seem that long to wait to get a likely definitive answer.  But at the least show us the EXPLAIN without ANALYZE of it, that should take no milliseconds.

And what does it mean for something to take 5 minutes longer than "never finishes"?

(Also, putting every or every other token on a separate line does not make it easier to read)

Cheer,

Jeff



--

Mickael van der Beek

Web developer & Security analyst

pgsql-performance by date:

Previous
From: Jeff Janes
Date:
Subject: Re: Array of integer indexed nested-loop semi join
Next
From: André Hänsel
Date:
Subject: Unworkable plan above certain row count