Re: index prefetching

On 1/22/24 08:21, Konstantin Knizhnik wrote:
>
> 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.
>

Yes.

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

Not sure I agree with describing this as "exotic".

The same thing applies to an arbitrary function call. And those are
pretty common in conditions - date_part/date_trunc. Arithmetic
expressions are not that uncommon either. Also, users sometimes have
conditions comparing multiple keys (a<b) etc.

But even if it was "uncommon", the whole point of this patch is to
eliminate these corner cases where a user does something minor (like
adding an output column), and the executor disables an optimization
unnecessarily, causing unexpected regressions.

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

I'm not sure what you mean. The patch does that, more or less. There's
issues that need to be solved (e.g. to decide when not to do this), and
how to integrate that into the scan interface (where the quals are
evaluated at the end).

What do you mean when you say "will not be easy to implement"? What
problems do you foresee?

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company