public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Korotkov <[email protected]>
To: jian he <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Andrei Lepikhov <[email protected]>
Cc: Alena Rybakina <[email protected]>
Cc: Nikolay Shaplov <[email protected]>
Cc: [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: Tue, 8 Oct 2024 17:00:08 +0300
Message-ID: <CAPpHfdsB2e20Y4jThsonD3+smwwisYWJbJN_mpGjm=JiT7OQaQ@mail.gmail.com> (raw)
In-Reply-To: <CACJufxE_VHObCqw4VFK2zONCdYnAkY7w+mq+5jf82eWn5EF8Vw@mail.gmail.com>
References: <[email protected]>
<[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>
<[email protected]>
<CAPpHfdvF864n=Lzmjd2XBi9TwboZvrhRtLSt2hCP+JVUv6XKzg@mail.gmail.com>
<CACJufxHCJvC3X8nUK-jRvRru-ZEXp16EBPADOwTGaqmOYM1Raw@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAPpHfduBA9RXa1LQAz=wFK8cGx_AaHVK_vnNqCnG4yA7KwFf7g@mail.gmail.com>
<[email protected]>
<CAPpHfds4wGdWQ2xx1OUKZC4ggqS6Q=cBSck9Re1ajY0ESkwcmw@mail.gmail.com>
<CA+TgmoZ3tQ3Zwzo+9uXQL_tf0rDHQHKeZ4bGt1LDvL+_dqghUg@mail.gmail.com>
<CAPpHfdsmmg6S8V63O3Z2j2bU9xyWd5mMaz68MfybQUY5e3iZVg@mail.gmail.com>
<CACJufxGz4xgb0=J3EG0fdPW_qc1qg2shXBQBpacCTa-4w4iosw@mail.gmail.com>
<CACJufxE_VHObCqw4VFK2zONCdYnAkY7w+mq+5jf82eWn5EF8Vw@mail.gmail.com>
Hi, Jian!
Thank you for your feedback.
On Tue, Oct 8, 2024 at 8:12 AM jian he <[email protected]> wrote:
>
> On Mon, Oct 7, 2024 at 10:06 PM jian he <[email protected]> wrote:
> >
> > assume v40 is the latest version.
>
> make_bitmap_paths_for_or_group
> {
> /*
> * First, try to match the whole group to the one index.
> */
> orargs = list_make1(ri);
> indlist = build_paths_for_OR(root, rel,
> orargs,
> other_clauses);
> if (indlist != NIL)
> {
> bitmapqual = choose_bitmap_and(root, rel, indlist);
> jointcost = bitmapqual->total_cost;
> jointlist = list_make1(bitmapqual);
> }
> /*
> * Also try to match all containing clauses 'one-by-one.
> */
> foreach(lc, args)
> {
> orargs = list_make1(lfirst(lc));
> indlist = build_paths_for_OR(root, rel,
> orargs,
> other_clauses);
> if (indlist == NIL)
> {
> splitlist = NIL;
> break;
> }
> bitmapqual = choose_bitmap_and(root, rel, indlist);
> }
>
> if other_clauses is not NIL, then "try to match all containing clauses
> 'one-by-one"
> the foreach loop "foreach(lc, args)" will apply other_clauses in
> build_paths_for_OR every time.
> then splitcost will obviously be higher than jointcost.
Some of other_clauses could match to some index column. So, the
splitcost could be lower than jointcost. Please check [1] test case,
but not it misses t_b_c_idx. So the correct full script is following.
create table t (a int not null, b int not null, c int not null);
insert into t (select 1, 1, i from generate_series(1,10000) i);
insert into t (select i, 2, 2 from generate_series(1,10000) i);
create index t_a_b_idx on t (a, b);
create index t_b_c_idx on t (b, c);
create statistics t_a_b_stat (mcv) on a, b from t;
create statistics t_b_c_stat (mcv) on b, c from t;
vacuum analyze t;
explain select * from t where a = 1 and (b = 1 or b = 2) and c = 2;
Also, note its possible that splitlist != NULL, but jointlist == NULL.
Check [2] for example.
>
> if other_clauses is NIL.
> "foreach(lc, args)" will have list_length(args) startup cost.
> So overall, it looks like jointcost will alway less than splitcost,
> the only corner case would be both are zero.
If other_clauses is NIL, we could probably do a shortcut when
jointlist != NULL. At least, I don't see the case why would we need
jointlist in this case at the first glance. Will investigate that
futher.
>
> anyway, in make_bitmap_paths_for_or_group,
> above line "Pick the best option." I added:
>
> if (splitcost <= jointcost && splitcost != 0 && jointcost != 0)
> elog(INFO, "%s:%d splitcost <= jointcost and both is not
> zero", __FILE_NAME__, __LINE__);
> and the regress tests passed.
> That means we don't need to iterate "((BoolExpr *)
> ri->orclause)->args" in make_bitmap_paths_for_or_group
> ?
Indeed, the regression test coverage is lacking. Your feedback is valuable.
Links.
1. https://www.postgresql.org/message-id/CAPpHfdtSXxhdv3mLOLjEewGeXJ%2BFtfhjqodn1WWuq5JLsKx48g%40mail.g...
2. https://www.postgresql.org/message-id/CAPpHfduJtO0s9E%3DSHUTzrCD88BH0eik0UNog1_q3XBF2wLmH6g%40mail.g...
------
Regards,
Alexander Korotkov
Supabase
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: <CAPpHfdsB2e20Y4jThsonD3+smwwisYWJbJN_mpGjm=JiT7OQaQ@mail.gmail.com>
* 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