Thread: int2vector and btree indexes

If I create btree index on a int2vector column, it does not get used for
queries because the query search always fails to match the index operator
(family).

During index creation, GetDefaultOpClass() returns array_ops for a
int2vector index column, because type int2vector is binary-coercible with
anyarray (which is array_ops's input type).  Whereas queries involving
int2vector columns would use a int2vector_ops operator.

I wonder if the index creation command should rather fail because an index
thus created will never get used?  Or is_indexable_operator() should
somehow consider the fact that such indexes could in fact exist?

I might be missing something though.

Thanks,
Amit

On 2016/10/11 15:58, Amit Langote wrote:
> If I create btree index on a int2vector column, it does not get used for
> queries because the query search always fails to match the index operator
> (family).
> 
> During index creation, GetDefaultOpClass() returns array_ops for a
> int2vector index column, because type int2vector is binary-coercible with
> anyarray (which is array_ops's input type).  Whereas queries involving
> int2vector columns would use a int2vector_ops operator.

I was wrong that the index *never* gets used.  It does in fact get used if
the operator is an ordering search operator (<, <=, >, >=), in which case
the query would use an array_ops operator (which is a btree operator class
for type anyarray) and hence matches the index operator family.  I failed
to mention in my original message that int2vector_ops is a hash operator
class.  There is exactly one =(int2vector, int2vector) operator in the
system of which there is no btree equivalent.

I guess there is not much to complaint about here after all.  Sorry about
the noise.

Thanks,
Amit

Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
> I was wrong that the index *never* gets used.  It does in fact get used if
> the operator is an ordering search operator (<, <=, >, >=), in which case
> the query would use an array_ops operator (which is a btree operator class
> for type anyarray) and hence matches the index operator family.  I failed
> to mention in my original message that int2vector_ops is a hash operator
> class.  There is exactly one =(int2vector, int2vector) operator in the
> system of which there is no btree equivalent.

Hmm ... I kind of wonder why we have int2vectoreq or hashint2vector at
all, likewise the hash opclass based on them.  The code says that they
are needed to support catcache index columns, but the only columns of
this type are

regression=# select attrelid::regclass,attname from pg_attribute where atttypid = 'int2vector'::regtype; attrelid  |
attname 
 
------------+-----------pg_index   | indkeypg_index   | indoptionpg_trigger | tgattr
(3 rows)

and those don't have indexes at all, let alone catcaches based on them.
So it looks to me like we could remove this infrastructure.  There is
value in being able to hash int2vectors during queries, for sure, but
we could let that be done by the anyarray hash opclass.

Having said that, int2vector is not meant as a user-facing type and so
I don't particularly care whether indexes built on it work conveniently.
But it looks to me like we've got some unnecessary code here.
        regards, tom lane

On 2016/10/11 21:40, Tom Lane wrote:
> Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
>> I was wrong that the index *never* gets used.  It does in fact get used if
>> the operator is an ordering search operator (<, <=, >, >=), in which case
>> the query would use an array_ops operator (which is a btree operator class
>> for type anyarray) and hence matches the index operator family.  I failed
>> to mention in my original message that int2vector_ops is a hash operator
>> class.  There is exactly one =(int2vector, int2vector) operator in the
>> system of which there is no btree equivalent.
>
> Hmm ... I kind of wonder why we have int2vectoreq or hashint2vector at
> all, likewise the hash opclass based on them.  The code says that they
> are needed to support catcache index columns, but the only columns of
> this type are
>
> regression=# select attrelid::regclass,attname from pg_attribute where atttypid = 'int2vector'::regtype;
>   attrelid  |  attname
> ------------+-----------
>  pg_index   | indkey
>  pg_index   | indoption
>  pg_trigger | tgattr
> (3 rows)
>
> and those don't have indexes at all, let alone catcaches based on them.
> So it looks to me like we could remove this infrastructure.  There is
> value in being able to hash int2vectors during queries, for sure, but
> we could let that be done by the anyarray hash opclass.

Agreed.  So how about the attached patch to remove the said infrastructure?

> Having said that, int2vector is not meant as a user-facing type and so
> I don't particularly care whether indexes built on it work conveniently.
> But it looks to me like we've got some unnecessary code here.

Ah, I did wonder whether int2vector has been deprecated as a user-facing
type.  Anyway after applying the patch, it seems that the original
complaint I raised is no longer an issue (or so I think) - operators
applied to int2vector are always resolved to those accepting anyarray and
matched with anyarray_ops of the correct index access method.

Thanks,
Amit

Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
> On 2016/10/11 21:40, Tom Lane wrote:
>> Hmm ... I kind of wonder why we have int2vectoreq or hashint2vector at
>> all, likewise the hash opclass based on them.

> Agreed.  So how about the attached patch to remove the said infrastructure?

Looks good, pushed.
        regards, tom lane