pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Harmen <harmen@lijzij.de>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: row estimate for partial index
Date: Sat, 14 Jan 2023 11:23:07 -0500
Message-ID: <1008139.1673713387@sss.pgh.pa.us> (raw)
In-Reply-To: <20230113150159.GJ91568@arp.lijzij.de>
References: <20230113150159.GJ91568@arp.lijzij.de>

Harmen <harmen@lijzij.de> writes:
> Works well enough. However, we now have an org_id which has > 10% of the rows,
> but only a handful rows where "deleted is null" matches (so the org has a lot
> of "deleted" contacts). The planner doesn't like this and it falls back to a
> full table scan for the above query.

> I've added a dedicated index just for that org_id, to see if that helps:
>     "org123" btree (id) WHERE deleted IS NULL AND org_id = 123
> The planner seems to use it now, however the row estimate is way off:

Yeah, so that indicates that it isn't producing a good selectivity
estimate for the combination of those two conditions: it will assume
the org_id and deleted columns are independent, which per your statements
they are not.

If you are running a reasonably recent PG version you should be able to
fix that by setting up "extended statistics" on that pair of columns:

https://www.postgresql.org/docs/current/planner-stats.html#PLANNER-STATS-EXTENDED

(I might be wrong, but I think that will help even when one of
the troublesome conditions is a null-check.  If it doesn't, then
we have something to improve there ...)

			regards, tom lane





view thread (5+ messages)  latest in thread

Message-ID: <1008139.1673713387@sss.pgh.pa.us>
Permalink:  ../1008139.1673713387@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1008139.1673713387@sss.pgh.pa.us

 · 

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-general@postgresql.org
  Cc: tgl@sss.pgh.pa.us, harmen@lijzij.de
  Subject: Re: row estimate for partial index
  In-Reply-To: <1008139.1673713387@sss.pgh.pa.us>
  References: <20230113150159.GJ91568@arp.lijzij.de> <1008139.1673713387@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