Thread

1. rfd: multi-key GiST index problems

   Oleg Bartunov <oleg@sai.msu.su> — 2001-05-23T16:14:41Z

   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
   
   
   
   

2. Re: rfd: multi-key GiST index problems

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-23T17:40:45Z

   > 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
   
   

3. Re: Re: rfd: multi-key GiST index problems

   Oleg Bartunov <oleg@sai.msu.su> — 2001-05-25T09:39:43Z

   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