From: Alexander Okulovich <aokulovich@stiltsoft.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres 15 SELECT query doesn't use index under RLS
Date: Tue, 31 Oct 2023 17:01:29 +0100
Message-ID: <6c888a16-b206-4817-b5ca-9e09b904edde@stiltsoft.com> (raw)
In-Reply-To: <1157086.1698329377@sss.pgh.pa.us>
References: <5c1179bb-240b-4c1c-b4b3-2a24868e44bc@stiltsoft.com>
<1570249.1697228785@sss.pgh.pa.us>
<dd874d42-ad02-48a6-82db-5666f1ee0ec1@stiltsoft.com>
<3153246.1697661350@sss.pgh.pa.us>
<e9d503cb-efeb-43d3-952e-f517e4d24302@stiltsoft.com>
<1157086.1698329377@sss.pgh.pa.us>
Hi Tom,
> Can you force it in either direction with "set enable_seqscan = off"
> (resp. "set enable_indexscan = off")? If so, how do the estimated
> costs compare for the two plan shapes?
Here are the results from the prod instance:
seqscan off <https://explain.depesz.com/s/9AWx;
indexscan_off <https://explain.depesz.com/s/mTU2;
Just noticed that the WHEN clause differs from the initial one (392 ids
under RLS). Probably, this is why the execution time isn't so
catastrophic. Please let me know if this matters, and I'll rerun this
with the initial request.
Speaking of the stage vs local Docker Postgres instance, the execution
time on stage is so short (0.1 ms with seq scan, 0.195 with index scan)
that we probably should not consider them. But I'll execute the requests
if it's necessary.
> Maybe your prod installation has a bloated index, and that's driving
> up the estimated cost enough to steer the planner away from it.
We tried to make REINDEX CONCURRENTLY on a prod copy, but the planner
still used Seq Scan instead of Index Scan afterward.
Kind regards,
Alexander
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: aokulovich@stiltsoft.com, tgl@sss.pgh.pa.us
Subject: Re: Postgres 15 SELECT query doesn't use index under RLS
In-Reply-To: <6c888a16-b206-4817-b5ca-9e09b904edde@stiltsoft.com>
* 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