From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marc Millas <marc.millas@mokadb.com>
Cc: Erik Wienhold <ewie@ewie.name>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: impact join syntax ?? and gist index ??
Date: Sat, 07 Jan 2023 17:07:16 -0500
Message-ID: <3484388.1673129236@sss.pgh.pa.us> (raw)
In-Reply-To: <CADX_1aYeLc7rzV0zU=_FubFw7i6Ge2R_eVr2vzgvbG31EFo0nQ@mail.gmail.com>
References: <CADX_1aabU_Og_tCEKX4MK=jkq1stgwFmxi0RsX5Qq8g1C1=LBg@mail.gmail.com>
<832577793.126542.1673124009766@office.mailbox.org>
<CADX_1abdY44bNmLVW07u0GqWqJWMhH6q+MHfByP=macs7wur+Q@mail.gmail.com>
<3481346.1673127177@sss.pgh.pa.us>
<CADX_1aYeLc7rzV0zU=_FubFw7i6Ge2R_eVr2vzgvbG31EFo0nQ@mail.gmail.com>
Marc Millas <marc.millas@mokadb.com> writes:
> I read your answer, Tom, but I cannot connect it to my measurements: why
> adding the index did slow the request twice ??
Are you referring to
>>> if I do create a gist index on geometry column of the big table,
>>> both syntax takes 21 seconds.
? That result is pretty much what I'd expect. If the planner has
to form a nestloop-with-inner-indexscan between a small table and
a big one, it's pretty much always going to prefer to put the small
table on the outside of the loop if it can. The cost of such a
loop is going to be more or less number of outer rows times the
log of the number of inner rows (assuming at great risk of
oversimplification that an index probe into a table of size N
requires about O(log N) work), and it's not hard to see that
S * log(B) is less than B * log(S). You seem to have lucked into a
case where the other way comes out faster despite that, which perhaps
can be explained by buffering effects, but it's not something to bet
on across-the-board.
(If you see this holding consistently, maybe it'd be advisable to
reduce the planner's effective_cache_size parameter to something
closer to shared_buffers, as it seems to indicate that fetches
from kernel space are pretty expensive on your platform.)
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, marc.millas@mokadb.com, ewie@ewie.name
Subject: Re: impact join syntax ?? and gist index ??
In-Reply-To: <3484388.1673129236@sss.pgh.pa.us>
References: <CADX_1aabU_Og_tCEKX4MK=jkq1stgwFmxi0RsX5Qq8g1C1=LBg@mail.gmail.com> <832577793.126542.1673124009766@office.mailbox.org> <CADX_1abdY44bNmLVW07u0GqWqJWMhH6q+MHfByP=macs7wur+Q@mail.gmail.com> <3481346.1673127177@sss.pgh.pa.us> <CADX_1aYeLc7rzV0zU=_FubFw7i6Ge2R_eVr2vzgvbG31EFo0nQ@mail.gmail.com> <3484388.1673129236@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox