Re: Showing primitive index scan count in EXPLAIN ANALYZE (for skip scan and SAOP scans)

On 09.11.2024 21:46, Peter Geoghegan wrote:
> On Sat, Nov 9, 2024 at 12:37 PM Alena Rybakina
> <a(dot)rybakina(at)postgrespro(dot)ru> wrote:
>> I noticed that the "Index Searches" cases shown in the regression tests are only for partitioned tables, maybe something you should add some tests for regular tables like tenk1.
> I allowed the patch on this thread to bitrot, but I've been
> maintaining this same patch as part of the skip scan patchset.
> Attached is the latest version of this patch (technically this is the
> first patch in the skip scan patch series). Just to keep things
> passing on the CFTester app.
Thank you)
>
> I haven't done anything about the implementation (still using a
> counter that lives in IndexScanDesc) due to a lack of clarity on
> what'll work best.
I've been still researching this to be honest and also haven't yet
opinion when the counter will be more suitable.
> Hopefully discussion of those aspects of this patch
> will pick up again soon.

I hope too.

> Note that I have changed the patch to divide "Index Searches:" by
> nloops, since Tomas Vondra seemed to want to do it that way
> (personally I don't feel strongly about that either way). So that's
> one behavioral change, not seen in any of the versions of the patch
> that have been posted to this thread so far.

Or maybe I was affected by fatigue, but I don’t understand this point,
to be honest. I see from the documentation and your first letter that it
specifies how many times in total the tuple search would be performed
during the index execution. Is that not quite right?

The documentation:

 <para>
    <command>EXPLAIN ANALYZE</command> breaks down the total number of
index
    searches performed by each index scan node. <literal>Index
Searches: N</literal>
    indicates the total number of searches across <emphasis>all</emphasis>
    executor node executions/loops.
   </para>
>> In general, I support the initiative to display this information in the query plan output. I think it is necessary for finding the reasons for low query performance.
> I just know that if Postgres 18 has skip scan, but doesn't have basic
> instrumentation of the number of index searches in EXPLAIN ANALYZE
> when skip scan is in use, we're going to get lots of complaints about
> it. It'll be very different from the current status quo. My main
> motivation here is to avoid complaints about the behavior of skip scan
> being completely opaque to users.
Yes, we can expect users to be concerned about this, but it is wrong not
to display information about it at all. The right thing to do is to see
the problem and try to solve it in the future.
I think this patch is the first step towards a solution, right?
It may also encourage the user to consider other options for solving
this problem, such as not to use index scan (for example, use
pg_hint_plan extension) or building a view from this table or something
else, if it significantly harms their performance.
> I think that the same issue could also happen with your OR
> transformation patch, if we don't get this EXPLAIN ANALYZE
> instrumentation. Users will still naturally want to know if a query
> "WHERE a = 2 OR a = 4 OR a = 6" required only one index search during
> its index scan, or if it required as many as 3 searches. They can
> already see this information with a BitmapOr-based plan, today.
>
> Why wouldn't they expect to continue to see the same information (or
> similar information) when the index searches happen to be coordinated
> by the index scan node/index AM itself?
>
To be honest, I don't quite understand this. Can you please explain in
more detail?

--
Regards,
Alena Rybakina
Postgres Professional