Re: index prefetching

On 22/01/2024 1:39 am, Tomas Vondra wrote:
>> Why we can prefer covering index  to compound index? I see only two good
>> reasons:
>> 1. Extra columns type do not  have comparison function need for AM.
>> 2. The extra columns are never used in query predicate.
>>
> Or maybe you don't want to include the columns in a UNIQUE constraint?
>
Do you mean that compound index (a,b) can not be used to enforce
uniqueness of "a"?
If so, I agree.

>> If you are going to use this columns in query predicates I do not see
>> much sense in creating inclusive index rather than compound index.
>> Do you?
>>
> But this is also about conditions that can't be translated into index
> scan keys. Consider this:
>
> create table t (a int, b int, c int);
> insert into t select 1000 * random(), 1000 * random(), 1000 * random()
> from generate_series(1,1000000) s(i);
> create index on t (a,b);
> vacuum analyze t;
>
> explain (analyze, buffers) select * from t where a = 10 and mod(b,10) =
> 1111111;
> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------
> Index Scan using t_a_b_idx on t (cost=0.42..3670.74 rows=5 width=12)
> (actual time=4.562..4.564 rows=0 loops=1)
> Index Cond: (a = 10)
> Filter: (mod(b, 10) = 1111111)
> Rows Removed by Filter: 974
> Buffers: shared hit=980
> Prefetches: blocks=901
> Planning Time: 0.304 ms
> Execution Time: 5.146 ms
> (8 rows)
>
> Notice that this still fetched ~1000 buffers in order to evaluate the
> filter on "b", because it's complex and can't be transformed into a nice
> scan key.

O yes.
Looks like I didn't understand the logic when predicate is included in
index condition and when not.
It seems to be natural that only such predicate which specifies some
range can be included in index condition.
But it is not the case:

postgres=# explain select * from t where a = 10 and b in (10,20,30);
QUERY PLAN
---------------------------------------------------------------------
Index Scan using t_a_b_idx on t (cost=0.42..25.33 rows=3 width=12)
Index Cond: ((a = 10) AND (b = ANY ('{10,20,30}'::integer[])))
(2 rows)

So I though ANY predicate using index keys is included in index condition.
But it is not true (as your example shows).

But IMHO mod(b,10)=111111 or (b+1) < 100 are both quite rare predicates
this is why I named this use cases "exotic".

In any case, if we have some columns in index tuple it is desired to use
them for filtering before extracting heap tuple.
But I afraid it will be not so easy to implement...