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

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.

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. Hopefully discussion of those aspects of this patch
will pick up again soon.

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.

> 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.

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?

--
Peter Geoghegan