Thread: rfd: multi-key GiST index problems

Tom,

we're hardly working on multi-key support in GiST and horizon is
being closer :-) I'd like to raise several questions:

1. index_getprocid (backend/access/index/idexam.c) doesn't
   properly supports multi-keys indexes with procnum > 1
   it's works only if either procnum=1 (B-tree, hash) or attnum=1
   (Rtree,Gist,hash). But for multi-key GiST we have
   7>=procnum >=1 and attnum > 1

   We've found workaround for GiST by using define, but general solution
   requires knowledge of the number of procedures for given type of index
   We didn't find a place where this number is stored in the index structure.
   if it's not index it's necessary to add it to the index structure.

2. it's necessary to recognize if index attribute requires checking
   for lossines. Consider following:
   create index a on table using gist(a gist__int_ops) with (islossy);
   create index b on table using gist(b gist_box_ops);

   create index c on table using gist(b gist_box_ops,a gist__int_ops) with (islossy);

   gist__int_ops uses compression with lossy, so we need to check heap tuple
   after successful checking of index tuple, but gist_box_ops doesn't
   requires such test. In third example with multi-key index we
   forced to use 'with (islossy)' for all index even if select will
   use index by first attribute (b gist_box_ops) which is a not right
   thing.
   We'd like to specify lossy attribute for each attribute of index
   something like:
   create index c on table using gist(b gist_box_ops,a gist__int_ops with (islossy));
   Accordingly executor should understand and process this syntax.

Current status:

we could create multi-key GiST index and search is working for 1st attribute
as in current version. there is a problem with searching on next attributes
because currently StrategyNumber (contains, overlap, less ..etc) doesn't
determined for these attributes. StrategyNumber is used for method
Consistence. We hope to resolve this problem.

    Regards,
        Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

> 1. index_getprocid (backend/access/index/idexam.c) doesn't
>    properly supports multi-keys indexes with procnum > 1
>    it's works only if either procnum=1 (B-tree, hash) or attnum=1

It looks to me like IndexSupportInitialize and index_getprocid have
different ideas about whether procnum or attnum is the major index
of the array :-(.  Change one or the other.

>    We didn't find a place where this number is stored in the index structure.

indexRelation->rd_am->amsupport, cf. InitIndexStrategy.

>    In third example with multi-key index we
>    forced to use 'with (islossy)' for all index even if select will
>    use index by first attribute (b gist_box_ops) which is a not right
>    thing.

islossy is a per-index attribute, not a per-column attribute.  I don't
think it makes sense to define it any other way.  If any one of the
columns is stored in a lossy fashion, then the index is lossy.

            regards, tom lane

On Wed, 23 May 2001, Tom Lane wrote:

>
> indexRelation->rd_am->amsupport, cf. InitIndexStrategy.

Thanks Tom

>
> >    In third example with multi-key index we
> >    forced to use 'with (islossy)' for all index even if select will
> >    use index by first attribute (b gist_box_ops) which is a not right
> >    thing.
>
> islossy is a per-index attribute, not a per-column attribute.  I don't
> think it makes sense to define it any other way.  If any one of the
> columns is stored in a lossy fashion, then the index is lossy.

Not always. If we have multi-key index and only 2nd column requires lossy
why do we need to check lossiness if select only 1st column ?
It's not a high priority, but some optimization would be fine.


>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

    Regards,
        Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83