pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alexander Korotkov <akorotkov@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Improve GIN cost estimation
Date: Sun, 08 Jan 2023 19:52:00 +0000
Message-ID: <E1pEbi4-002gV9-3i@gemulon.postgresql.org> (raw)

Improve GIN cost estimation

GIN index scans were not taking any descent CPU-based cost into account.  That
made them look cheaper than other types of indexes when they shouldn't be.

We use the same heuristic as for btree indexes, but multiply it by the number
of searched entries.

Additionally, the CPU cost for the tree was based largely on a
genericcostestimate.  For a GIN index, we should not charge index quals per
tuple, but per entry. On top of this, charge cpu_index_tuple_cost per actual
tuple.

This should fix the cases where a GIN index is preferred over a btree and
the ones where a memoize node is not added on top of the GIN index scan
because it seemed too cheap.

We don't packpatch this to evade unexpected plan changes in stable versions.

Discussion: https://postgr.es/m/CABs3KGQnOkyQ42-zKQqiE7M0Ks9oWDSee%3D%2BJx3-TGq%3D68xqWYw%40mail.gmail.com
Discussion: https://postgr.es/m/3188617.44csPzL39Z%40aivenronan
Author: Ronan Dunklau
Reported-By: Hung Nguyen
Reviewed-by: Tom Lane, Alexander Korotkov

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/cd9479af2af25d7fa9bfd24dd4dcf976b360f077

Modified Files
--------------
src/backend/utils/adt/selfuncs.c | 69 +++++++++++++++++++++++++++++++++++++---
1 file changed, 64 insertions(+), 5 deletions(-)



Message-ID: <E1pEbi4-002gV9-3i@gemulon.postgresql.org>
Permalink:  ../E1pEbi4-002gV9-3i@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1pEbi4-002gV9-3i@gemulon.postgresql.org

 · 

reply

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-committers@postgresql.org
  Cc: akorotkov@postgresql.org
  Subject: Re: pgsql: Improve GIN cost estimation
  In-Reply-To: <E1pEbi4-002gV9-3i@gemulon.postgresql.org>
  References: <E1pEbi4-002gV9-3i@gemulon.postgresql.org>

* 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