pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Harmen <harmen@lijzij.de>
To: pgsql-general@lists.postgresql.org
Subject: row estimate for partial index
Date: Fri, 13 Jan 2023 16:01:59 +0100
Message-ID: <20230113150159.GJ91568@arp.lijzij.de> (raw)

Hi,

I'm trying to figure out why a certain query doesn't use a partial index.

The context:
The table has about 100M rows with "contacts". Every contact belongs to an
"org", and can be marked "deleted". Since we're generally only interested in
non-deleted contacts we have a (partial) index on the table:

    "contact_organization_id" btree (org_id, id) WHERE deleted IS NULL

for queries such as: "select * from contacts where org_id=123 and deleted is null order by id"

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:

    my_db=> explain SELECT c.id, COALESCE(c.first_name, '') FROM contacts AS c WHERE c.organization_id = 123 AND c.deleted IS NULL ORDER BY id ASC;
                                                 QUERY PLAN                                             
    ────────────────────────────────────────────────────────────────────────────────────────────────────
     Gather Merge  (cost=1761204.72..1883324.98 rows=1046672 width=36)
       Workers Planned: 2
       ->  Sort  (cost=1760204.70..1761513.04 rows=523336 width=36)
             Sort Key: id
             ->  Parallel Bitmap Heap Scan on contact c  (cost=318.13..1696183.14 rows=523336 width=36)
                   Recheck Cond: ((deleted IS NULL) AND (organization_id = 8448))
                   ->  Bitmap Index Scan on org123  (cost=0.00..4.13 rows=1256006 width=0)
    (7 rows)

    Time: 3.337 ms

That "1256006" estimate from the org123 index is wrong. There are 7 contacts in
that org:
               ->  Bitmap Index Scan on org8448  (cost=0.00..4.13 rows=1256006 width=0) (actual time=0.841..0.841 rows=7 loops=1)


Is this expected? Is there a way to improve this?

Thanks!
Harmen





view thread (5+ messages)  latest in thread

Message-ID: <20230113150159.GJ91568@arp.lijzij.de>
Permalink:  ../20230113150159.GJ91568@arp.lijzij.de/
Also on:    postgresql.org/message-id/20230113150159.GJ91568@arp.lijzij.de

 · 

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: harmen@lijzij.de
  Subject: Re: row estimate for partial index
  In-Reply-To: <20230113150159.GJ91568@arp.lijzij.de>
  References: <20230113150159.GJ91568@arp.lijzij.de>

* 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