Re: Use of additional index columns in rows filtering

On 8/8/23 19:43, Peter Geoghegan wrote:
> On Mon, Aug 7, 2023 at 9:21 PM Peter Geoghegan <pg@bowt.ie> wrote:
>> I see that get_op_btree_interpretation() will treat != as a kind of
>> honorary member of an opfamily whose = operator has our != operator as
>> its negator. Perhaps we should be finding a way to pass != quals into
>> the index AM so that they become true index quals (obviously they
>> would only be index filter predicates, never access predicates). That
>> has the advantage of working in a way that's analogous to the way that
>> index quals already avoid visibility checks.
> 
> The approach in your patch can only really work with index scans (and
> index-only scans). So while it is more general than true index quals
> in some ways, it's also less general in other ways: it cannot help
> bitmap index scans.
> 
> While I accept that the inability of bitmap index scans to use index
> filters in this way is, to some degree, a natural and inevitable
> downside of bitmap index scans, that isn't always true. For example,
> it doesn't seem to be the case with simple inequalities. Bitmap index
> scans argue for making cases involving quals that are "index quals in
> spirit" into actual index quals. Even if you can reliably avoid extra
> heap accesses for plain index scans using expression evaluation, I
> can't see that working for bitmap index scans.
> 
> More concretely, if we have an index on "tenk1 (four, two)", then we
> miss out on the opportunity to eliminate heap accesses for a query
> like this one:
> 
> select
>   ctid, *
> from
>   tenk1
> where
>   four = 1 and two != 1;
> 
> This will get a bitmap index scan plan (that uses our composite
> index), which makes sense overall. But the details beyond that make no
> sense -- since we're using table filter quals for "two". It turns out
> that the bitmap heap scan will access every single heap page in the
> tenk1 table as a result, even though we could have literally avoided
> all heap accesses had we been able to push down the != as an index
> qual. This is a difference in "buffers hit" that is close to 2 orders
> of magnitude.
> 
> I'd be okay with treating these cases as out of scope for this patch,
> but we should probably agree on the parameters. The patch certainly
> shouldn't make it any harder to fix cases such as this.
> 

I agree this patch shouldn't make it harder to improve these cases, but
TBH I don't quite see which part of the patch would do that? Which bit
are you objecting to? If we decide to change how match_clause_to_index()
deals with these cases, to recognize them as index quals, the patch will
be working just fine.

The only thing the patch does is it looks at clauses we decided not to
treat as index quals, and do maybe still evaluate them on index. And I
don't think I want to move these goalposts much further.


regards

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