From: Tom Lane <tgl@sss.pgh.pa.us>
To: Frits Jalvingh <jal@etc.to>
cc: "pgsql-performa." <pgsql-performance@postgresql.org>
Subject: Re: Postgres using nested loops despite setting enable_nestloop to
 false
In-reply-to: 
 <CAKhTGFUzjFaDon6cjF7NsWhDA0yXUHbhbAF66CZdaVK_v_YqVQ@mail.gmail.com>
References: 
 <CAKhTGFXJh6QfZTTXw4Qb-7oiFwgqh7Pdz0nUKjZnayuZpwjGqg@mail.gmail.com>
 <20201117142024.GA24784@telsasoft.com>
 <CAKhTGFUzjFaDon6cjF7NsWhDA0yXUHbhbAF66CZdaVK_v_YqVQ@mail.gmail.com>
Comments: In-reply-to Frits Jalvingh <jal@etc.to>
	message dated "Tue, 17 Nov 2020 16:58:45 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <919167.1605630107.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 17 Nov 2020 11:21:47 -0500
Message-ID: <919168.1605630107@sss.pgh.pa.us>
Precedence: bulk

Frits Jalvingh <jal@etc.to> writes:
> I have attached both plans, both made with set enable_nestloop =3D false=
 in
> the attachments.

The reason why you're getting a nested loop is that the planner has no
other choice.  The "tijd" table has no join conditions that would be
amenable to hash- or merge-joining it to something else, because both
of those join methods require a plain equality join condition.  AFAICS
in a quick look, all of tijd's join conditions look more like

    Join Filter: ((di04238cluster.dv_start_dts <=3D tijd.einddatum) AND (d=
i04238cluster.dv_end_dts > tijd.einddatum))

which is not amenable to anything except brute force cross-join-and-
test-the-condition.

Given that, it's likely that "enable_nestloop =3D false" is making things
worse not better, by artificially distorting the plan shape.

Seeing the large number of joins involved, I wonder what your
geqo_threshold, join_collapse_limit, and from_collapse_limit settings
are, and whether you can get a better plan by increasing them.

The planner doesn't seem to think that any of these joins involve
a very large number of rows, so I doubt that your work_mem setting
is very relevant.  However, are these rowcount estimates accurate?
You claimed upthread that you were dealing with hundreds of millions
of rows, but it's impossible to credit that cost estimates like

  ->  Seq Scan on s_h_cluster_ssm di01905cluster  (cost=3D0.00..155.05 row=
s=3D1072 width=3D24)
        Filter: (soort =3D 'FIN'::text)

correspond to scanning large tables.

In the end, I fear that finding a way to get rid of those
inequality join conditions may be your only real answer.

			regards, tom lane