Message-ID: <74e3c8bc-dccd-45e5-ad2d-6a6e96fc5864@postgrespro.ru>
Date: Mon, 11 Mar 2024 19:43:39 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: POC, WIP: OR-clause support for indexes
Content-Language: en-US
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: jian he <jian.universality@gmail.com>,
 Alena Rybakina <a.rybakina@postgrespro.ru>,
 Robert Haas <robertmhaas@gmail.com>, pgsql-hackers@postgresql.org,
 Peter Geoghegan <pg@bowt.ie>, "Finnerty, Jim" <jfinnert@amazon.com>,
 Marcos Pegoraro <marcos@f10.com.br>, teodor@sigaev.ru,
 Tomas Vondra <tomas.vondra@enterprisedb.com>,
 Peter Eisentraut <peter@eisentraut.org>, Ranier Vilela <ranier.vf@gmail.com>
References: <567ED6CA.2040504@sigaev.ru>
 <CAEudQAq4XTcwhw2OmfW1AJNqvMfbReEWjuzqv6YTz6ZjbpmL0g@mail.gmail.com>
 <21088129-7cd3-424a-bcce-6a3427ba3276@postgrespro.ru>
 <CAEudQArk002t_C9LGoeJWnw=iGKikt+gSa8Mh==muG00WS=qYA@mail.gmail.com>
 <CACJufxFhQn0awSEcZD7=FHvjx6OLt6PUtuoi0_10pWFVXUEDJw@mail.gmail.com>
 <ded629b2-7885-4be2-b5f5-9426ff47f72c@postgrespro.ru>
 <11ee9c04-281f-43c8-a0ff-d481c1e01c09@postgrespro.ru>
 <CACJufxGqLA32zTtaJRL2Cacp771k6PJnau7gsax0KRV434Qq7w@mail.gmail.com>
 <54009ba5-4aea-4344-a30d-e3558a59e2a9@postgrespro.ru>
 <380e5683-cdfd-4220-ab9b-7cccb9d3e480@postgrespro.ru>
 <CACJufxE6eG3ATnTfO9ubs7CjyfgjpCQrSt85oU33EZj00PdcbQ@mail.gmail.com>
 <e394ac5b-7ae2-4f64-a8ff-51fc934bba3e@postgrespro.ru>
 <d5d3f257-a2d8-4776-9c97-83f98e74636c@postgrespro.ru>
 <CAPpHfdtACtU_XbuNf9mgDBsTH1cN=i+uASXKBX_rvQk6zQ3MdQ@mail.gmail.com>
 <7389d0dd-05d5-41b7-a12d-2e73f939f851@postgrespro.ru>
 <CAPpHfdsAWGF2VvcszNofc32Sa2pefBpgY1nw2aYV=FTG7Q3wyA@mail.gmail.com>
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <CAPpHfdsAWGF2VvcszNofc32Sa2pefBpgY1nw2aYV=FTG7Q3wyA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/74e3c8bc-dccd-45e5-ad2d-6a6e96fc5864%40postgrespro.ru>
Precedence: bulk

On 11/3/2024 18:31, Alexander Korotkov wrote:
>> I'm not convinced about this limit. The initial reason was to combine
>> long lists of ORs into the array because such a transformation made at
>> an early stage increases efficiency.
>> I understand the necessity of this limit in the array decomposition
>> routine but not in the creation one.
> 
> The comment near MAX_SAOP_ARRAY_SIZE says that this limit is because
> N^2 algorithms could be applied to arrays.  Are you sure that's not
> true for our case?
When you operate an array, indeed. But when we transform ORs to an 
array, not. Just check all the places in the optimizer and even the 
executor where we would pass along the list of ORs. This is why I think 
we should use this optimization even more intensively for huge numbers 
of ORs in an attempt to speed up the overall query.

>>> 3) Better save the original order of clauses by putting hash entries and
>>> untransformable clauses to the same list.  A lot of differences in
>>> regression tests output have gone.
>> I agree that reducing the number of changes in regression tests looks
>> better. But to achieve this, you introduced a hack that increases the
>> complexity of the code. Is it worth it? Maybe it would be better to make
>> one-time changes in tests instead of getting this burden on board. Or
>> have you meant something more introducing the node type?
> 
> For me the reason is not just a regression test.  The current code
> keeps the original order of quals as much as possible.  The OR
> transformation code reorders quals even in cases when it doesn't
> eventually apply any optimization.  I don't think that's acceptable.
> However, less hackery ways for this is welcome for sure.
Why is it unacceptable? Can the user implement some order-dependent 
logic with clauses, and will it be correct?
Otherwise, it is a matter of taste, and generally, this decision is up 
to you.
> 
>>> We don't make array values unique.  That might make query execution
>>> performance somewhat worse, and also makes selectivity estimation
>>> worse.  I suggest Andrei and/or Alena should implement making array
>>> values unique.
>> The fix Alena has made looks correct. But I urge you to think twice:
>> The optimizer doesn't care about duplicates, so why do we do it?
>> What's more, this optimization is intended to speed up queries with long
>> OR lists. Using the list_append_unique() comparator on such lists could
>> impact performance. I suggest sticking to the common rule and leaving
>> the responsibility on the user's shoulders.
> 
> I don't see why the optimizer doesn't care about duplicates for OR
> lists.  As I showed before in an example, it successfully removes the
> duplicate.  So, currently OR transformation clearly introduces a
> regression in terms of selectivity estimation.  I think we should
> evade that.
I think you are right. It is probably a better place than any other to 
remove duplicates in an array. I just think we should sort and remove 
duplicates from entry->consts in one pass. Thus, this optimisation 
should be applied to sortable constants.

> 
>> At least, we should do this optimization later, in one pass, with
>> sorting elements before building the array. But what if we don't have a
>> sort operator for the type?
> 
> It was probably discussed before, but can we do our work later?  There
> is a canonicalize_qual() which calls find_duplicate_ors().  This is
> the place where currently duplicate OR clauses are removed.  Could our
> OR-to-ANY transformation be just another call from
> canonicalize_qual()?
Hmm, we already tried to do it at that point. I vaguely recall some 
issues caused by this approach. Anyway, it should be done as quickly as 
possible to increase the effect of the optimization.

-- 
regards,
Andrei Lepikhov
Postgres Professional