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