Horton

Re: index prefetching

Konstantin Knizhnik <knizhnik@garret.ru>

View thread
From: Konstantin Knizhnik <knizhnik@garret.ru>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-01-22T07:21:14Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. aio: io_uring: Trigger async processing for large IOs

  2. read stream: Split decision about look ahead for AIO and combining

  3. read_stream: Only increase read-ahead distance when waiting for IO

  4. read_stream: Prevent distance from decaying too quickly

  5. Reduce ExecSeqScan* code size using pg_assume()

  6. Fix rare bug in read_stream.c's split IO handling.

  7. Fix multiranges to behave more like dependent types.

  8. Add EXPLAIN (MEMORY) to report planner memory consumption

  9. Optimize nbtree backward scan boundary cases.

  10. Increment xactCompletionCount during subtransaction abort.

  11. Add nbtree Valgrind buffer lock checks.

  12. Add nbtree high key "continuescan" optimization.

  13. Reduce pinning and buffer content locking for btree scans.

  14. Teach btree to handle ScalarArrayOpExpr quals natively.

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