Re: Use of additional index columns in rows filtering

On 2/15/23 09:57, Maxim Ivanov wrote:
> Hi All,
>
> I'd like to report what seems to be a missing optimization
> opportunity or understand why it is not possible to achieve.
>
> TLDR; additional index column B specified in CREATE INDEX .. (A)
> INCLUDE(B) is not used to filter rows in queries like WHERE B = $1
> ORDER BY A during IndexScan. https://dbfiddle.uk/iehtq44L
>
> ...
>
> Here is the plan (notice high "shared hit"):
>
> Limit (cost=0.42..10955.01 rows=1 width=12) (actual time=84.283..84.284 rows=0 loops=1)
>    Output: a, b, d
>    Buffers: shared hit=198307
>    -> Index Scan using t_a_include_b on public.t (cost=0.42..10955.01 rows=1 width=12) (actual time=84.280..84.281 rows=0 loops=1)
>          Output: a, b, d
>          Index Cond: (t.a > 1000000)
>          Filter: (t.b = 4)
>          Rows Removed by Filter: 197805
>          Buffers: shared hit=198307
> Planning:
>    Buffers: shared hit=30
> Planning Time: 0.201 ms
> Execution Time: 84.303 ms
>

Yeah. The reason for this behavior is pretty simple:

1) When matching clauses to indexes in match_clause_to_index(), we only
look at key columns (nkeycolumns). We'd need to check all columns
(ncolumns) and remember if the clause matched a key or included one.

2) index_getnext_slot would need to get "candidate" TIDs using
conditions on keys, and then check the clauses on included
columns.

Seems doable, unless I'm missing some fatal issue.

regards

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