Content-Type: multipart/alternative;
 boundary="------------ZzHClUl0BPCwmx8RmGyixxV0"
Message-ID: <6c888a16-b206-4817-b5ca-9e09b904edde@stiltsoft.com>
Date: Tue, 31 Oct 2023 17:01:29 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Postgres 15 SELECT query doesn't use index under RLS
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
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>
Content-Language: en-US
From: Alexander Okulovich <aokulovich@stiltsoft.com>
In-Reply-To: <1157086.1698329377@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/6c888a16-b206-4817-b5ca-9e09b904edde%40stiltsoft.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------ZzHClUl0BPCwmx8RmGyixxV0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------ZzHClUl0BPCwmx8RmGyixxV0
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi Tom,</p>
    <p>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">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?</pre>
      </blockquote>
      Here are the results from the prod instance:</p>
    <p><a href="https://explain.depesz.com/s/9AWx">seqscan off</a><br>
    </p>
    <p><a href="https://explain.depesz.com/s/mTU2">indexscan_off</a></p>
    <p>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.</p>
    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.
    <p>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">Maybe your prod installation has a bloated index, and that's driving
up the estimated cost enough to steer the planner away from it.</pre>
      </blockquote>
      We tried to make REINDEX CONCURRENTLY on a prod copy, but the
      planner still used Seq Scan instead of Index Scan afterward.<br>
    </p>
    <p>Kind regards,</p>
    <p>Alexander</p>
  </body>
</html>

--------------ZzHClUl0BPCwmx8RmGyixxV0--