You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@paimon.apache.org by yejunhao <44...@qq.com.INVALID> on 2024/03/14 09:51:59 UTC
[DISCUSS] PIP-17: Introduce secondary column index
Hi, Paimon Devs, I’d like to start a discussion about PIP-17[1].
Up to now, Paimon use zorder & order & hilbert sort compaction to speed up query. After sort compaction, files will be sorted by the order of specified columns. But in some situations, for example, we have tens of columns that should be added in the filter column, sometimes all of them come up together, sometimes, just a few of them. Zorder or order compaction can't handle this situation, because too many columns will reduce the effect of sorting. So if the column base number of these columns is small, we can use bloomfilter or other indexes to speed up queries. That's why this PIP comes up. I want to introduce an index framework to support paimon with flexible index system.
Look forward to your question and suggestions.
Best, junhao
[1] https://cwiki.apache.org/confluence/display/PAIMON/PIP-17%3A+Introduce+secondary+column+index
Re: [DISCUSS] PIP-17: Introduce secondary column index
Posted by JUNHAO YE <ye...@gmail.com>.
Hi Yong Fang,
I am still working on the feature. For now, file index framework as beed added for columns.
And after testing, bloom-filter could actually speed up queries. Bloom-filter is ready.
There are several issues I am working on:
1、Procedures to add and remove file index for existing table.
2、Add more type of index (Not sure what to add, maybe use lucene to speed up queries for "where column_a like %xxx%")
3、Push this feature for starrocks and other compute engines. Starrocks may need a c++ type of index reader.
> 2024年5月10日 上午10:20，Yong Fang <zj...@gmail.com> 写道：
>
> Hi yejunhao,
>
> I'd like to know what's the status of this feature, are you still working
> on it? Thanks
>
> Best,
> Fang Yong
>
> On Wed, Mar 20, 2024 at 12:14 PM Aitozi <gj...@gmail.com> wrote:
>
>> Thanks for your inputs, I have no other questions, +1 for this.
>> Looking forward to this feature.
>>
>> Best,
>> Aitozi.
>>
>> JUNHAO YE <ye...@gmail.com> 于2024年3月20日周三 10:19写道：
>>
>>> Hi，aitozi
>>>
>>> Really thanks for comment! I have read your question and reply here:
>>>
>>> (1) For now, the secondary index is mainly designed for append-only
>> table.
>>> More and more users migrate from hive and hudi to paimon, their main
>> table
>>> format is append-only. In the future, after deletion files down, I think
>>> the secondary
>>> index is also useful for primary key with deletion file.
>>> See PIP-16 (
>>>
>> https://cwiki.apache.org/confluence/display/PAIMON/PIP-16%3A+Paimon+position+delete+mode
>>> )
>>> But that's not the job of this period. I should add this to PIP.
>>>
>>> (2) The answer is yes. I refer to the approach of Hudi and Delta Lake.
>>> Hudi put the index bytes in the user meta space of orc file and parquet
>>> file,
>>> delta lake use an extra file to support index, as a result, I want it
>> more
>>> flexible.
>>> Indeed, it will cause the x2 file numbers, but the file it self will not
>>> be touched
>>> often. Maybe later in the future, we can consider to combine these index
>>> file
>>> to reduce the pressure for filesystem, but I think we can implement it
>>> this way
>>> for now.
>>>
>>> (3) Correct. If you want drop one column index (this does not happen
>>> often),
>>> we just rewrite the index file, then discard the corresponding bytes,
>>> last, write it
>>> back to file and rewrite DataFileMeta in ManifestEntry.
>>>
>>> Thanks again for comment!
>>>
>>> Best,
>>> Junhao
>>>
>>>
>>>
>>>
>>>> 2024年3月19日 下午11:07，Aitozi <gj...@gmail.com> 写道：
>>>>
>>>> Hi, junhao
>>>>
>>>> I's nice to see the secondary index feature in paimon. After read
>> the
>>>> PIP, I have several questions here.
>>>>
>>>> (1) For the primary key table, we only push down the filter for the
>>> primary
>>>> key, because,
>>>> we can not filter the value if the value should be merged with other
>>>> levels data. So will
>>>> the primary key table be benefit from the secondary column index ? Or
>> the
>>>> main improvement
>>>> is for the append table ?
>>>>
>>>> (2) The storage of the index file, "one file for one datafile of one
>>> index
>>>> type", will this bring too much
>>>> extra files, an index type will x2 the file number ?
>>>>
>>>> (3) "While drop column index, for example, I have indexed column a and
>>> b, I
>>>> don't want to index a anymore. I just need to drop the target index
>> bytes
>>>> from index file,
>>>> and don't have to read the data file again."
>>>>
>>>> Do you mean we will have to rewrite the index file when drop one column
>>>> index in it ?
>>>>
>>>> Best,
>>>> Aitozi
>>>>
>>>> JUNHAO YE <ye...@gmail.com> 于2024年3月19日周二 19:26写道：
>>>>
>>>>> Hi, Zhang YiLong
>>>>>
>>>>> You are right, as I mentioned in PIP-17. We should have priority of
>>>>> different index types. We should consider about combine the result of
>>>>> different index type.
>>>>>
>>>>> Best, junhao.
>>>>>
>>>>>
>>>>>> 2024年3月18日 上午10:49，Zhang YiLong <zy...@hotmail.com> 写道：
>>>>>>
>>>>>> This is a big improvement, but I don't think it's for low cardinal
>>>>> fields, because the index at the file level, and for low cardinal
>> fields
>>>>> (e.g. gender is only male and female) in most cases (the field is not
>>>>> sorted) it is present in all files.
>>>>>>
>>>>>> For specific business, we wants a json index, bitmap index, reverse
>>>>> index, etc to adapt to different query conditions. So we also need a
>>>>> priority, using different indexes for different query filter and
>> finally
>>>>> combining the results (based on the actual filter criteria and/or)
>>>>>>
>>>>>> ________________________________
>>>>>> 发件人: yu zelin <yu...@gmail.com>
>>>>>> 发送时间: 2024年3月15日 14:43
>>>>>> 收件人: dev@paimon.apache.org <de...@paimon.apache.org>
>>>>>> 主题: Re: [DISCUSS] PIP-17: Introduce secondary column index
>>>>>>
>>>>>> An exciting feature, +1.
>>>>>>
>>>>>> Best Regards,
>>>>>> Zelin Yu
>>>>>>
>>>>>> On Thu, Mar 14, 2024 at 5:53 PM yejunhao <44...@qq.com.invalid>
>>>>> wrote:
>>>>>>
>>>>>>> Hi, Paimon Devs, I’d like to start a discussion about PIP-17[1].
>>>>>>>
>>>>>>> Up to now, Paimon use zorder & order & hilbert sort compaction to
>>> speed
>>>>> up
>>>>>>> query. After sort compaction, files will be sorted by the order of
>>>>>>> specified columns. But in some situations, for example, we have tens
>>> of
>>>>>>> columns that should be added in the filter column, sometimes all of
>>> them
>>>>>>> come up together, sometimes, just a few of them. Zorder or order
>>>>> compaction
>>>>>>> can't handle this situation, because too many columns will reduce
>> the
>>>>>>> effect of sorting. So if the column base number of these columns is
>>>>> small,
>>>>>>> we can use bloomfilter or other indexes to speed up queries. That's
>>> why
>>>>>>> this PIP comes up. I want to introduce an index framework to support
>>>>> paimon
>>>>>>> with flexible index system.
>>>>>>>
>>>>>>> Look forward to your question and suggestions.
>>>>>>>
>>>>>>> Best, junhao
>>>>>>>
>>>>>>> [1]
>>>>>>>
>>>>>
>>>
>> https://cwiki.apache.org/confluence/display/PAIMON/PIP-17%3A+Introduce+secondary+column+index
>>>>>
>>>>>
>>>
>>>
>>
Re: [DISCUSS] PIP-17: Introduce secondary column index
Posted by Yong Fang <zj...@gmail.com>.
Hi yejunhao,
I'd like to know what's the status of this feature, are you still working
on it? Thanks
Best,
Fang Yong
On Wed, Mar 20, 2024 at 12:14 PM Aitozi <gj...@gmail.com> wrote:
> Thanks for your inputs, I have no other questions, +1 for this.
> Looking forward to this feature.
>
> Best,
> Aitozi.
>
> JUNHAO YE <ye...@gmail.com> 于2024年3月20日周三 10:19写道：
>
> > Hi，aitozi
> >
> > Really thanks for comment! I have read your question and reply here:
> >
> > (1) For now, the secondary index is mainly designed for append-only
> table.
> > More and more users migrate from hive and hudi to paimon, their main
> table
> > format is append-only. In the future, after deletion files down, I think
> > the secondary
> > index is also useful for primary key with deletion file.
> > See PIP-16 (
> >
> https://cwiki.apache.org/confluence/display/PAIMON/PIP-16%3A+Paimon+position+delete+mode
> > )
> > But that's not the job of this period. I should add this to PIP.
> >
> > (2) The answer is yes. I refer to the approach of Hudi and Delta Lake.
> > Hudi put the index bytes in the user meta space of orc file and parquet
> > file,
> > delta lake use an extra file to support index, as a result, I want it
> more
> > flexible.
> > Indeed, it will cause the x2 file numbers, but the file it self will not
> > be touched
> > often. Maybe later in the future, we can consider to combine these index
> > file
> > to reduce the pressure for filesystem, but I think we can implement it
> > this way
> > for now.
> >
> > (3) Correct. If you want drop one column index (this does not happen
> > often),
> > we just rewrite the index file, then discard the corresponding bytes,
> > last, write it
> > back to file and rewrite DataFileMeta in ManifestEntry.
> >
> > Thanks again for comment!
> >
> > Best,
> > Junhao
> >
> >
> >
> >
> > > 2024年3月19日 下午11:07，Aitozi <gj...@gmail.com> 写道：
> > >
> > > Hi, junhao
> > >
> > > I's nice to see the secondary index feature in paimon. After read
> the
> > > PIP, I have several questions here.
> > >
> > > (1) For the primary key table, we only push down the filter for the
> > primary
> > > key, because,
> > > we can not filter the value if the value should be merged with other
> > > levels data. So will
> > > the primary key table be benefit from the secondary column index ? Or
> the
> > > main improvement
> > > is for the append table ?
> > >
> > > (2) The storage of the index file, "one file for one datafile of one
> > index
> > > type", will this bring too much
> > > extra files, an index type will x2 the file number ?
> > >
> > > (3) "While drop column index, for example, I have indexed column a and
> > b, I
> > > don't want to index a anymore. I just need to drop the target index
> bytes
> > > from index file,
> > > and don't have to read the data file again."
> > >
> > > Do you mean we will have to rewrite the index file when drop one column
> > > index in it ?
> > >
> > > Best,
> > > Aitozi
> > >
> > > JUNHAO YE <ye...@gmail.com> 于2024年3月19日周二 19:26写道：
> > >
> > >> Hi, Zhang YiLong
> > >>
> > >> You are right, as I mentioned in PIP-17. We should have priority of
> > >> different index types. We should consider about combine the result of
> > >> different index type.
> > >>
> > >> Best, junhao.
> > >>
> > >>
> > >>> 2024年3月18日 上午10:49，Zhang YiLong <zy...@hotmail.com> 写道：
> > >>>
> > >>> This is a big improvement, but I don't think it's for low cardinal
> > >> fields, because the index at the file level, and for low cardinal
> fields
> > >> (e.g. gender is only male and female) in most cases (the field is not
> > >> sorted) it is present in all files.
> > >>>
> > >>> For specific business, we wants a json index, bitmap index, reverse
> > >> index, etc to adapt to different query conditions. So we also need a
> > >> priority, using different indexes for different query filter and
> finally
> > >> combining the results (based on the actual filter criteria and/or)
> > >>>
> > >>> ________________________________
> > >>> 发件人: yu zelin <yu...@gmail.com>
> > >>> 发送时间: 2024年3月15日 14:43
> > >>> 收件人: dev@paimon.apache.org <de...@paimon.apache.org>
> > >>> 主题: Re: [DISCUSS] PIP-17: Introduce secondary column index
> > >>>
> > >>> An exciting feature, +1.
> > >>>
> > >>> Best Regards,
> > >>> Zelin Yu
> > >>>
> > >>> On Thu, Mar 14, 2024 at 5:53 PM yejunhao <44...@qq.com.invalid>
> > >> wrote:
> > >>>
> > >>>> Hi, Paimon Devs, I’d like to start a discussion about PIP-17[1].
> > >>>>
> > >>>> Up to now, Paimon use zorder & order & hilbert sort compaction to
> > speed
> > >> up
> > >>>> query. After sort compaction, files will be sorted by the order of
> > >>>> specified columns. But in some situations, for example, we have tens
> > of
> > >>>> columns that should be added in the filter column, sometimes all of
> > them
> > >>>> come up together, sometimes, just a few of them. Zorder or order
> > >> compaction
> > >>>> can't handle this situation, because too many columns will reduce
> the
> > >>>> effect of sorting. So if the column base number of these columns is
> > >> small,
> > >>>> we can use bloomfilter or other indexes to speed up queries. That's
> > why
> > >>>> this PIP comes up. I want to introduce an index framework to support
> > >> paimon
> > >>>> with flexible index system.
> > >>>>
> > >>>> Look forward to your question and suggestions.
> > >>>>
> > >>>> Best, junhao
> > >>>>
> > >>>> [1]
> > >>>>
> > >>
> >
> https://cwiki.apache.org/confluence/display/PAIMON/PIP-17%3A+Introduce+secondary+column+index
> > >>
> > >>
> >
> >
>
Re: [DISCUSS] PIP-17: Introduce secondary column index
Posted by Aitozi <gj...@gmail.com>.
Thanks for your inputs, I have no other questions, +1 for this.
Looking forward to this feature.
Best,
Aitozi.
JUNHAO YE <ye...@gmail.com> 于2024年3月20日周三 10:19写道：
> Hi，aitozi
>
> Really thanks for comment! I have read your question and reply here:
>
> (1) For now, the secondary index is mainly designed for append-only table.
> More and more users migrate from hive and hudi to paimon, their main table
> format is append-only. In the future, after deletion files down, I think
> the secondary
> index is also useful for primary key with deletion file.
> See PIP-16 (
> https://cwiki.apache.org/confluence/display/PAIMON/PIP-16%3A+Paimon+position+delete+mode
> )
> But that's not the job of this period. I should add this to PIP.
>
> (2) The answer is yes. I refer to the approach of Hudi and Delta Lake.
> Hudi put the index bytes in the user meta space of orc file and parquet
> file,
> delta lake use an extra file to support index, as a result, I want it more
> flexible.
> Indeed, it will cause the x2 file numbers, but the file it self will not
> be touched
> often. Maybe later in the future, we can consider to combine these index
> file
> to reduce the pressure for filesystem, but I think we can implement it
> this way
> for now.
>
> (3) Correct. If you want drop one column index (this does not happen
> often),
> we just rewrite the index file, then discard the corresponding bytes,
> last, write it
> back to file and rewrite DataFileMeta in ManifestEntry.
>
> Thanks again for comment!
>
> Best,
> Junhao
>
>
>
>
> > 2024年3月19日 下午11:07，Aitozi <gj...@gmail.com> 写道：
> >
> > Hi, junhao
> >
> > I's nice to see the secondary index feature in paimon. After read the
> > PIP, I have several questions here.
> >
> > (1) For the primary key table, we only push down the filter for the
> primary
> > key, because,
> > we can not filter the value if the value should be merged with other
> > levels data. So will
> > the primary key table be benefit from the secondary column index ? Or the
> > main improvement
> > is for the append table ?
> >
> > (2) The storage of the index file, "one file for one datafile of one
> index
> > type", will this bring too much
> > extra files, an index type will x2 the file number ?
> >
> > (3) "While drop column index, for example, I have indexed column a and
> b, I
> > don't want to index a anymore. I just need to drop the target index bytes
> > from index file,
> > and don't have to read the data file again."
> >
> > Do you mean we will have to rewrite the index file when drop one column
> > index in it ?
> >
> > Best,
> > Aitozi
> >
> > JUNHAO YE <ye...@gmail.com> 于2024年3月19日周二 19:26写道：
> >
> >> Hi, Zhang YiLong
> >>
> >> You are right, as I mentioned in PIP-17. We should have priority of
> >> different index types. We should consider about combine the result of
> >> different index type.
> >>
> >> Best, junhao.
> >>
> >>
> >>> 2024年3月18日 上午10:49，Zhang YiLong <zy...@hotmail.com> 写道：
> >>>
> >>> This is a big improvement, but I don't think it's for low cardinal
> >> fields, because the index at the file level, and for low cardinal fields
> >> (e.g. gender is only male and female) in most cases (the field is not
> >> sorted) it is present in all files.
> >>>
> >>> For specific business, we wants a json index, bitmap index, reverse
> >> index, etc to adapt to different query conditions. So we also need a
> >> priority, using different indexes for different query filter and finally
> >> combining the results (based on the actual filter criteria and/or)
> >>>
> >>> ________________________________
> >>> 发件人: yu zelin <yu...@gmail.com>
> >>> 发送时间: 2024年3月15日 14:43
> >>> 收件人: dev@paimon.apache.org <de...@paimon.apache.org>
> >>> 主题: Re: [DISCUSS] PIP-17: Introduce secondary column index
> >>>
> >>> An exciting feature, +1.
> >>>
> >>> Best Regards,
> >>> Zelin Yu
> >>>
> >>> On Thu, Mar 14, 2024 at 5:53 PM yejunhao <44...@qq.com.invalid>
> >> wrote:
> >>>
> >>>> Hi, Paimon Devs, I’d like to start a discussion about PIP-17[1].
> >>>>
> >>>> Up to now, Paimon use zorder & order & hilbert sort compaction to
> speed
> >> up
> >>>> query. After sort compaction, files will be sorted by the order of
> >>>> specified columns. But in some situations, for example, we have tens
> of
> >>>> columns that should be added in the filter column, sometimes all of
> them
> >>>> come up together, sometimes, just a few of them. Zorder or order
> >> compaction
> >>>> can't handle this situation, because too many columns will reduce the
> >>>> effect of sorting. So if the column base number of these columns is
> >> small,
> >>>> we can use bloomfilter or other indexes to speed up queries. That's
> why
> >>>> this PIP comes up. I want to introduce an index framework to support
> >> paimon
> >>>> with flexible index system.
> >>>>
> >>>> Look forward to your question and suggestions.
> >>>>
> >>>> Best, junhao
> >>>>
> >>>> [1]
> >>>>
> >>
> https://cwiki.apache.org/confluence/display/PAIMON/PIP-17%3A+Introduce+secondary+column+index
> >>
> >>
>
>
Re: [DISCUSS] PIP-17: Introduce secondary column index
Posted by JUNHAO YE <ye...@gmail.com>.
Hi，aitozi
Really thanks for comment! I have read your question and reply here:
(1) For now, the secondary index is mainly designed for append-only table.
More and more users migrate from hive and hudi to paimon, their main table
format is append-only. In the future, after deletion files down, I think the secondary
index is also useful for primary key with deletion file.
See PIP-16 (https://cwiki.apache.org/confluence/display/PAIMON/PIP-16%3A+Paimon+position+delete+mode)
But that's not the job of this period. I should add this to PIP.
(2) The answer is yes. I refer to the approach of Hudi and Delta Lake.
Hudi put the index bytes in the user meta space of orc file and parquet file,
delta lake use an extra file to support index, as a result, I want it more flexible.
Indeed, it will cause the x2 file numbers, but the file it self will not be touched
often. Maybe later in the future, we can consider to combine these index file
to reduce the pressure for filesystem, but I think we can implement it this way
for now.
(3) Correct. If you want drop one column index (this does not happen often),
we just rewrite the index file, then discard the corresponding bytes, last, write it
back to file and rewrite DataFileMeta in ManifestEntry.
Thanks again for comment!
Best,
Junhao
> 2024年3月19日 下午11:07，Aitozi <gj...@gmail.com> 写道：
>
> Hi, junhao
>
> I's nice to see the secondary index feature in paimon. After read the
> PIP, I have several questions here.
>
> (1) For the primary key table, we only push down the filter for the primary
> key, because,
> we can not filter the value if the value should be merged with other
> levels data. So will
> the primary key table be benefit from the secondary column index ? Or the
> main improvement
> is for the append table ?
>
> (2) The storage of the index file, "one file for one datafile of one index
> type", will this bring too much
> extra files, an index type will x2 the file number ?
>
> (3) "While drop column index, for example, I have indexed column a and b, I
> don't want to index a anymore. I just need to drop the target index bytes
> from index file,
> and don't have to read the data file again."
>
> Do you mean we will have to rewrite the index file when drop one column
> index in it ?
>
> Best,
> Aitozi
>
> JUNHAO YE <ye...@gmail.com> 于2024年3月19日周二 19:26写道：
>
>> Hi, Zhang YiLong
>>
>> You are right, as I mentioned in PIP-17. We should have priority of
>> different index types. We should consider about combine the result of
>> different index type.
>>
>> Best, junhao.
>>
>>
>>> 2024年3月18日 上午10:49，Zhang YiLong <zy...@hotmail.com> 写道：
>>>
>>> This is a big improvement, but I don't think it's for low cardinal
>> fields, because the index at the file level, and for low cardinal fields
>> (e.g. gender is only male and female) in most cases (the field is not
>> sorted) it is present in all files.
>>>
>>> For specific business, we wants a json index, bitmap index, reverse
>> index, etc to adapt to different query conditions. So we also need a
>> priority, using different indexes for different query filter and finally
>> combining the results (based on the actual filter criteria and/or)
>>>
>>> ________________________________
>>> 发件人: yu zelin <yu...@gmail.com>
>>> 发送时间: 2024年3月15日 14:43
>>> 收件人: dev@paimon.apache.org <de...@paimon.apache.org>
>>> 主题: Re: [DISCUSS] PIP-17: Introduce secondary column index
>>>
>>> An exciting feature, +1.
>>>
>>> Best Regards,
>>> Zelin Yu
>>>
>>> On Thu, Mar 14, 2024 at 5:53 PM yejunhao <44...@qq.com.invalid>
>> wrote:
>>>
>>>> Hi, Paimon Devs, I’d like to start a discussion about PIP-17[1].
>>>>
>>>> Up to now, Paimon use zorder & order & hilbert sort compaction to speed
>> up
>>>> query. After sort compaction, files will be sorted by the order of
>>>> specified columns. But in some situations, for example, we have tens of
>>>> columns that should be added in the filter column, sometimes all of them
>>>> come up together, sometimes, just a few of them. Zorder or order
>> compaction
>>>> can't handle this situation, because too many columns will reduce the
>>>> effect of sorting. So if the column base number of these columns is
>> small,
>>>> we can use bloomfilter or other indexes to speed up queries. That's why
>>>> this PIP comes up. I want to introduce an index framework to support
>> paimon
>>>> with flexible index system.
>>>>
>>>> Look forward to your question and suggestions.
>>>>
>>>> Best, junhao
>>>>
>>>> [1]
>>>>
>> https://cwiki.apache.org/confluence/display/PAIMON/PIP-17%3A+Introduce+secondary+column+index
>>
>>
Re: [DISCUSS] PIP-17: Introduce secondary column index
Posted by Aitozi <gj...@gmail.com>.
Hi, junhao
I's nice to see the secondary index feature in paimon. After read the
PIP, I have several questions here.
(1) For the primary key table, we only push down the filter for the primary
key, because,
we can not filter the value if the value should be merged with other
levels data. So will
the primary key table be benefit from the secondary column index ? Or the
main improvement
is for the append table ?
(2) The storage of the index file, "one file for one datafile of one index
type", will this bring too much
extra files, an index type will x2 the file number ?
(3) "While drop column index, for example, I have indexed column a and b, I
don't want to index a anymore. I just need to drop the target index bytes
from index file,
and don't have to read the data file again."
Do you mean we will have to rewrite the index file when drop one column
index in it ?
Best,
Aitozi
JUNHAO YE <ye...@gmail.com> 于2024年3月19日周二 19:26写道：
> Hi, Zhang YiLong
>
> You are right, as I mentioned in PIP-17. We should have priority of
> different index types. We should consider about combine the result of
> different index type.
>
> Best, junhao.
>
>
> > 2024年3月18日 上午10:49，Zhang YiLong <zy...@hotmail.com> 写道：
> >
> > This is a big improvement, but I don't think it's for low cardinal
> fields, because the index at the file level, and for low cardinal fields
> (e.g. gender is only male and female) in most cases (the field is not
> sorted) it is present in all files.
> >
> > For specific business, we wants a json index, bitmap index, reverse
> index, etc to adapt to different query conditions. So we also need a
> priority, using different indexes for different query filter and finally
> combining the results (based on the actual filter criteria and/or)
> >
> > ________________________________
> > 发件人: yu zelin <yu...@gmail.com>
> > 发送时间: 2024年3月15日 14:43
> > 收件人: dev@paimon.apache.org <de...@paimon.apache.org>
> > 主题: Re: [DISCUSS] PIP-17: Introduce secondary column index
> >
> > An exciting feature, +1.
> >
> > Best Regards,
> > Zelin Yu
> >
> > On Thu, Mar 14, 2024 at 5:53 PM yejunhao <44...@qq.com.invalid>
> wrote:
> >
> >> Hi, Paimon Devs, I’d like to start a discussion about PIP-17[1].
> >>
> >> Up to now, Paimon use zorder & order & hilbert sort compaction to speed
> up
> >> query. After sort compaction, files will be sorted by the order of
> >> specified columns. But in some situations, for example, we have tens of
> >> columns that should be added in the filter column, sometimes all of them
> >> come up together, sometimes, just a few of them. Zorder or order
> compaction
> >> can't handle this situation, because too many columns will reduce the
> >> effect of sorting. So if the column base number of these columns is
> small,
> >> we can use bloomfilter or other indexes to speed up queries. That's why
> >> this PIP comes up. I want to introduce an index framework to support
> paimon
> >> with flexible index system.
> >>
> >> Look forward to your question and suggestions.
> >>
> >> Best, junhao
> >>
> >> [1]
> >>
> https://cwiki.apache.org/confluence/display/PAIMON/PIP-17%3A+Introduce+secondary+column+index
>
>
Re: [DISCUSS] PIP-17: Introduce secondary column index
Posted by JUNHAO YE <ye...@gmail.com>.
Hi, Zhang YiLong
You are right, as I mentioned in PIP-17. We should have priority of different index types. We should consider about combine the result of different index type.
Best, junhao.
> 2024年3月18日 上午10:49，Zhang YiLong <zy...@hotmail.com> 写道：
>
> This is a big improvement, but I don't think it's for low cardinal fields, because the index at the file level, and for low cardinal fields (e.g. gender is only male and female) in most cases (the field is not sorted) it is present in all files.
>
> For specific business, we wants a json index, bitmap index, reverse index, etc to adapt to different query conditions. So we also need a priority, using different indexes for different query filter and finally combining the results (based on the actual filter criteria and/or)
>
> ________________________________
> 发件人: yu zelin <yu...@gmail.com>
> 发送时间: 2024年3月15日 14:43
> 收件人: dev@paimon.apache.org <de...@paimon.apache.org>
> 主题: Re: [DISCUSS] PIP-17: Introduce secondary column index
>
> An exciting feature, +1.
>
> Best Regards,
> Zelin Yu
>
> On Thu, Mar 14, 2024 at 5:53 PM yejunhao <44...@qq.com.invalid> wrote:
>
>> Hi, Paimon Devs, I’d like to start a discussion about PIP-17[1].
>>
>> Up to now, Paimon use zorder & order & hilbert sort compaction to speed up
>> query. After sort compaction, files will be sorted by the order of
>> specified columns. But in some situations, for example, we have tens of
>> columns that should be added in the filter column, sometimes all of them
>> come up together, sometimes, just a few of them. Zorder or order compaction
>> can't handle this situation, because too many columns will reduce the
>> effect of sorting. So if the column base number of these columns is small,
>> we can use bloomfilter or other indexes to speed up queries. That's why
>> this PIP comes up. I want to introduce an index framework to support paimon
>> with flexible index system.
>>
>> Look forward to your question and suggestions.
>>
>> Best, junhao
>>
>> [1]
>> https://cwiki.apache.org/confluence/display/PAIMON/PIP-17%3A+Introduce+secondary+column+index
回复: [DISCUSS] PIP-17: Introduce secondary column index
Posted by Zhang YiLong <zy...@hotmail.com>.
This is a big improvement, but I don't think it's for low cardinal fields, because the index at the file level, and for low cardinal fields (e.g. gender is only male and female) in most cases (the field is not sorted) it is present in all files.
For specific business, we wants a json index, bitmap index, reverse index, etc to adapt to different query conditions. So we also need a priority, using different indexes for different query filter and finally combining the results (based on the actual filter criteria and/or)
________________________________
发件人: yu zelin <yu...@gmail.com>
发送时间: 2024年3月15日 14:43
收件人: dev@paimon.apache.org <de...@paimon.apache.org>
主题: Re: [DISCUSS] PIP-17: Introduce secondary column index
An exciting feature, +1.
Best Regards,
Zelin Yu
On Thu, Mar 14, 2024 at 5:53 PM yejunhao <44...@qq.com.invalid> wrote:
> Hi, Paimon Devs, I’d like to start a discussion about PIP-17[1].
>
> Up to now, Paimon use zorder & order & hilbert sort compaction to speed up
> query. After sort compaction, files will be sorted by the order of
> specified columns. But in some situations, for example, we have tens of
> columns that should be added in the filter column, sometimes all of them
> come up together, sometimes, just a few of them. Zorder or order compaction
> can't handle this situation, because too many columns will reduce the
> effect of sorting. So if the column base number of these columns is small,
> we can use bloomfilter or other indexes to speed up queries. That's why
> this PIP comes up. I want to introduce an index framework to support paimon
> with flexible index system.
>
> Look forward to your question and suggestions.
>
> Best, junhao
>
> [1]
> https://cwiki.apache.org/confluence/display/PAIMON/PIP-17%3A+Introduce+secondary+column+index
Re: [DISCUSS] PIP-17: Introduce secondary column index
Posted by yu zelin <yu...@gmail.com>.
An exciting feature, +1.
Best Regards,
Zelin Yu
On Thu, Mar 14, 2024 at 5:53 PM yejunhao <44...@qq.com.invalid> wrote:
> Hi, Paimon Devs, I’d like to start a discussion about PIP-17[1].
>
> Up to now, Paimon use zorder & order & hilbert sort compaction to speed up
> query. After sort compaction, files will be sorted by the order of
> specified columns. But in some situations, for example, we have tens of
> columns that should be added in the filter column, sometimes all of them
> come up together, sometimes, just a few of them. Zorder or order compaction
> can't handle this situation, because too many columns will reduce the
> effect of sorting. So if the column base number of these columns is small,
> we can use bloomfilter or other indexes to speed up queries. That's why
> this PIP comes up. I want to introduce an index framework to support paimon
> with flexible index system.
>
> Look forward to your question and suggestions.
>
> Best, junhao
>
> [1]
> https://cwiki.apache.org/confluence/display/PAIMON/PIP-17%3A+Introduce+secondary+column+index