pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Frédéric Yhuel <frederic.yhuel@dalibo.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Cc: Christophe Courtois <christophe.courtois@dalibo.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Subject: Re: Indexes on expressions with multiple columns and operators
Date: Thu, 18 Sep 2025 17:26:28 +0200
Message-ID: <20250918172628.44301672@karst> (raw)
In-Reply-To: <1507576.1758120083@sss.pgh.pa.us>
References: <a8b99dce-1bfb-4d97-af73-54a32b85c916@dalibo.com>
	<1507576.1758120083@sss.pgh.pa.us>

Hi there,

I think this discussion has a nice solution, thank you!

However, while poking around this issue yesterday, we also found something
surprising between estimated rows and costs when using a function. Bellow the
scenario to apply on top of Frederic's one to quickly expose the weirdness:

  CREATE OR REPLACE FUNCTION s(crit text, ackid int) 
  RETURNS bool LANGUAGE plpgsql IMMUTABLE AS $$
  BEGIN RETURN crit = 'WARNING' AND ackid IS NULL;
  END $$;

  CREATE INDEX foo_s_idx ON foo (s(crit, ackid));

  ANALYZE foo ;

  EXPLAIN SELECT * FROM foo WHERE s(crit, ackid);

  EXPLAIN SELECT * FROM foo
  WHERE (ackid IS NULL AND crit = 'WARNING') is true;

  SELECT most_common_vals, most_common_freqs
  FROM pg_stats WHERE tablename = 'foo_s_idx';

On a fresh instance from HEAD with its default configuration, it shows:

  Index Scan using foo_s_idx on foo  (cost=0.29..8.39 rows=33333 width=13)
    Index Cond: (s(crit, ackid) = true)

  Index Scan using foo_expr_idx on foo  (cost=0.29..8.39 rows=5 width=13)
    Index Cond: (((ackid IS NULL) AND (crit = 'WARNING'::text)) = true)

   most_common_vals | most_common_freqs 
  ------------------+-------------------
   {f,t}            | {0.99995,5e-05}

It seems statistics shown in "pg_stats" view for function "s()" are good. The
query itself even have the same costs than the query using the syntax tips you
provide before.

However, the estimated row number seems wrong in regard with the costs shown
and statistics. It looks like a default hardcoded 33% has been applied. As
Frederic said earlier, this bad row estimation drives the upper join to the
wrong method.

Is this a known planer behavior?

(again, the ju-jitsu syntax provided by Laurenz and you is definitely on
point here, I just hijack the thread to discuss this weirdness.)

Regards,





view thread (26+ messages)  latest in thread

Message-ID: <20250918172628.44301672@karst>
Permalink:  ../20250918172628.44301672@karst/
Also on:    postgresql.org/message-id/20250918172628.44301672@karst

 · 

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-performance@postgresql.org
  Cc: jgdr@dalibo.com, tgl@sss.pgh.pa.us, frederic.yhuel@dalibo.com, christophe.courtois@dalibo.com, laurenz.albe@cybertec.at
  Subject: Re: Indexes on expressions with multiple columns and operators
  In-Reply-To: <20250918172628.44301672@karst>
  References: <a8b99dce-1bfb-4d97-af73-54a32b85c916@dalibo.com> <1507576.1758120083@sss.pgh.pa.us> <20250918172628.44301672@karst>

* 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