public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andrei Lepikhov <[email protected]>
To: Alexander Korotkov <[email protected]>
Cc: Alena Rybakina <[email protected]>
Cc: Nikolay Shaplov <[email protected]>
Cc: [email protected]
Cc: jian he <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: [email protected]
Cc: Peter Geoghegan <[email protected]>
Cc: Marcos Pegoraro <[email protected]>
Cc: [email protected]
Cc: Peter Eisentraut <[email protected]>
Cc: Ranier Vilela <[email protected]>
Subject: Re: POC, WIP: OR-clause support for indexes
Date: Fri, 23 Aug 2024 12:23:49 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPpHfdtR3cBKCSj_7dd7EDF_pa-rUUQBx3-guK6kZZ=_hSeMfg@mail.gmail.com>
References: <[email protected]>
	<8969055.VV5PYv0bhD@thinkpad-pgpro>
	<[email protected]>
	<CAPpHfdsrtAB=0B-qYuQMPgyyy=-CycFJfzTWO_30nx27vXc3dA@mail.gmail.com>
	<[email protected]>
	<CAPpHfdvUEjkuFNUbg7GuLkXotwvpDgBYz4S8YcE=LRf9aBQh-Q@mail.gmail.com>
	<[email protected]>
	<CAPpHfdu5iQOjF93vGbjidsQkhHvY2NSm29duENYH_cbhC6x+Mg@mail.gmail.com>
	<CAPpHfdv9GrXfCOdPsMszBrORig+nW2+JBuMD-np_keVuKJ7bdQ@mail.gmail.com>
	<[email protected]>
	<CAPpHfduTM2W3kmiN1=5fvgcu6yA5=ghq82OkvLvZC8Mh9PpEuA@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAPpHfduFJHTNGCTFWt4GFs-E6Ts8xS5mCsKOYKBsZAmoOr8ZSA@mail.gmail.com>
	<[email protected]>
	<CAPpHfdtR3cBKCSj_7dd7EDF_pa-rUUQBx3-guK6kZZ=_hSeMfg@mail.gmail.com>

On 21/8/2024 16:52, Alexander Korotkov wrote:
>> /* Only operator clauses scan match */
>> Should it be:
>> /* Only operator clauses can match */
>> ?
> 
> Corrected, thanks.
I found one more: /* Only operator clauses scan match  */ - in the 
second patch.
Also I propose:
- “might match to the index as whole” -> “might match the index as a whole“
- Group similar OR-arguments intro dedicated RestrictInfos -> ‘into’
> 
>> The second one:
>> When creating IndexClause, we assign the original and derived clauses to
>> the new, containing transformed array. But logically, we should set the
>> clause with a list of ORs as the original. Why did you do so?
> 
> I actually didn't notice that.  Corrected to set the OR clause as the
> original.  That change turned recheck to use original OR clauses,
> probably better this way.  Also, that change spotted misuse of
> RestrictInfo.clause and RestrictInfo.orclause in the second patch.
> Corrected this too.
New findings:
=============

1)
if (list_length(clause->args) != 2)
	return NULL;
I guess, above we can 'continue' the process.

2) Calling the match_index_to_operand in three nested cycles you could 
break the search on first successful match, couldn't it? At least, the 
comment "just stop with first matching index key" say so.

3) I finally found the limit of this feature: the case of two partial 
indexes on the same column. Look at the example below:

SET enable_indexscan = 'off';
SET enable_seqscan = 'off';
DROP TABLE IF EXISTS test CASCADE;
CREATE TABLE test (x int);
INSERT INTO test (x) SELECT * FROM generate_series(1,100);
CREATE INDEX ON test (x) WHERE x < 80;
CREATE INDEX ON test (x) WHERE x > 80;
VACUUM ANALYZE test;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM test WHERE x=1 OR x = 79;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM test WHERE x=91 OR x = 81;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM test WHERE x=1 OR x = 81 OR x = 83;

The last query doesn't group clauses into two indexes. The reason is in 
match_index_to_operand which classifies all 'x=' to one class. I'm not 
sure because of overhead, but it may be resolved by using 
predicate_implied_by to partial indexes.

-- 
regards, Andrei Lepikhov

view thread (78+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: POC, WIP: OR-clause support for indexes
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox