Re: Improve EXPLAIN output for multicolumn B-Tree Index

+1 for the idea.

On Mon, 24 Jun 2024 at 11:11, Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
> I think this is too easy to confuse with the pre-existing 'Filter'
> condition, which you'll find on indexes with INCLUDE-d columns or
> filters on non-index columns.

Why not combine them? And both call them Filter? In a sense this
filtering acts very similar to INCLUDE based filtering (for btrees at
least). Although I might be wrong about that, because when I try to
confirm the same perf using the following script I do get quite
different timings (maybe you have an idea what's going on here). But
even if it does mean something slightly different perf wise, I think
using Filter for both is unlikely to confuse anyone. Since, while
allowed, it seems extremely unlikely in practice that someone will use
the same column as part of the indexed columns and as part of the
INCLUDE-d columns (why would you store the same info twice).

CREATE TABLE test (id1 int, id2 int, id3 int, value varchar(32));
INSERT INTO test (SELECT i % 10, i % 1000, i, 'hello' FROM
generate_series(1,1000000) s(i));
vacuum freeze test;
CREATE INDEX test_idx_include ON test(id1, id2) INCLUDE (id3);
ANALYZE test;
EXPLAIN (VERBOSE, ANALYZE, BUFFERS) SELECT id1, id3 FROM test WHERE
id1 = 1 AND id3 = 101;
CREATE INDEX test_idx ON test(id1, id2, id3);
ANALYZE test;
EXPLAIN (VERBOSE, ANALYZE, BUFFERS) SELECT id1, id3 FROM test WHERE
id1 = 1 AND id3 = 101;

                                                              QUERY PLAN
───────────────────────────────────────
 Index Only Scan using test_idx_include on public.test
(cost=0.42..3557.09 rows=1 width=8) (actual time=0.708..6.639 rows=1
loops=1)
   Output: id1, id3
   Index Cond: (test.id1 = 1)
   Filter: (test.id3 = 101)
   Rows Removed by Filter: 99999
   Heap Fetches: 0
   Buffers: shared hit=1 read=386
 Query Identifier: 471139784017641093
 Planning:
   Buffers: shared hit=8 read=1
 Planning Time: 0.091 ms
 Execution Time: 6.656 ms
(12 rows)

Time: 7.139 ms
                                                          QUERY PLAN
─────────────────────────────────────
 Index Only Scan using test_idx on public.test  (cost=0.42..2591.77
rows=1 width=8) (actual time=0.238..2.110 rows=1 loops=1)
   Output: id1, id3
   Index Cond: ((test.id1 = 1) AND (test.id3 = 101))
   Heap Fetches: 0
   Buffers: shared hit=1 read=386
 Query Identifier: 471139784017641093
 Planning:
   Buffers: shared hit=10 read=1
 Planning Time: 0.129 ms
 Execution Time: 2.128 ms
(10 rows)

Time: 2.645 ms