Re: Showing primitive index scan count in EXPLAIN ANALYZE (for skip scan and SAOP scans) - Mailing list pgsql-hackers

On Sat, Nov 9, 2024 at 12:37 PM Alena Rybakina
<a.rybakina@postgrespro.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