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

Hi!

On 27.08.2024 18:15, Peter Geoghegan wrote:
> On Thu, Aug 15, 2024 at 3:22 PM Peter Geoghegan<pg(at)bowt(dot)ie> wrote:
>> Attached patch has EXPLAIN ANALYZE display the total number of
>> primitive index scans for all 3 kinds of index scan node.
> Attached is v2, which fixes bitrot.
>
> v2 also uses new terminology. EXPLAIN ANALYZE will now show "Index
> Searches: N", not "Primitive Index Scans: N". Although there is
> limited precedent for using the primitive scan terminology, I think
> that it's a bit unwieldy.
>
> No other notable changes.

While reviewing the thread again, I noticed that the patch was applied
with conflicts. I fixed it. The updated version is in the
show_primitive_index.diff file.

You should look at why the test results in stats.out changed. To be
honest, I haven't investigated this deeply yet.

diff -U3
/home/alena/postgrespro__copy10/src/test/regress/expected/stats.out
/home/alena/postgrespro__copy10/src/test/regress/results/stats.out
--- /home/alena/postgrespro__copy10/src/test/regress/expected/stats.out
2024-11-09 17:45:03.812313004 +0300
+++ /home/alena/postgrespro__copy10/src/test/regress/results/stats.out
2024-11-09 18:05:02.129524219 +0300
@@ -673,7 +673,7 @@
 FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
  seq_scan | seq_ok | idx_scan | idx_ok
 ----------+--------+----------+--------
-        2 | t      |        1 | t
+        2 | t      |        2 | t
 (1 row)

 -- fetch timestamps from before the next test
@@ -716,7 +716,7 @@
 FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
  seq_scan | seq_ok | idx_scan | idx_ok
 ----------+--------+----------+--------
-        2 | t      |        2 | t
+        2 | t      |        4 | t
 (1 row)

 -- fetch timestamps from before the next test
@@ -761,7 +761,7 @@
 FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
  seq_scan | seq_ok | idx_scan | idx_ok
 ----------+--------+----------+--------
-        2 | t      |        3 | t
+        2 | t      |        6 | t
 (1 row)

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.

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.

--
Regards,
Alena Rybakina
Postgres Professional