From: | Andrei Lepikhov <lepihov(at)gmail(dot)com> |
---|---|
To: | Alexander Korotkov <aekorotkov(at)gmail(dot)com> |
Cc: | Alena Rybakina <a(dot)rybakina(at)postgrespro(dot)ru>, Nikolay Shaplov <dhyan(at)nataraj(dot)su>, pgsql-hackers(at)lists(dot)postgresql(dot)org, jian he <jian(dot)universality(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org, Peter Geoghegan <pg(at)bowt(dot)ie>, Marcos Pegoraro <marcos(at)f10(dot)com(dot)br>, teodor(at)sigaev(dot)ru, Peter Eisentraut <peter(at)eisentraut(dot)org>, Ranier Vilela <ranier(dot)vf(at)gmail(dot)com> |
Subject: | Re: POC, WIP: OR-clause support for indexes |
Date: | 2024-08-23 10:23:49 |
Message-ID: | 670d1dc2-a280-4cf6-bc29-e2eafb107081@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rafia Sabih | 2024-08-23 10:30:12 | Re: Trim the heap free memory |
Previous Message | Masahiko Sawada | 2024-08-23 10:13:22 | Re: Fix memory counter update in reorderbuffer |