Обсуждение: why provide cross type arithmetic operators

there are many cross type arithmetic operators, like int2 + int4, int8 +
int4, I think these can be deleted. Here are the reasons, after deleted,
int2 + int4 will choose the operator int4 + int4, int8 + int4 choose int8 +
int8, Is that ok? Thanks.

"ykhuang" <hyk@ruc.edu.cn> writes:

> there are many cross type arithmetic operators, like int2 + int4, int8 +
> int4, I think these can be deleted. Here are the reasons, after deleted,
> int2 + int4 will choose the operator int4 + int4, int8 + int4 choose int8 +
> int8, Is that ok? Thanks.

Then the system wouldn't be able to use indexes as flexibly. For example if
you have an index on an int2 column and perform a query with a restriction
like "int2col = 1" the system wouldn't find a matching =(int2,int4) operator
and would instead have to do a sequential scan casting the int2 column to an
int4 when checking each row.


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Gregory Stark <stark@enterprisedb.com> writes:
> "ykhuang" <hyk@ruc.edu.cn> writes:
>> there are many cross type arithmetic operators, like int2 + int4, int8 +
>> int4, I think these can be deleted. Here are the reasons, after deleted,
>> int2 + int4 will choose the operator int4 + int4, int8 + int4 choose int8 +
>> int8, Is that ok? Thanks.

> Then the system wouldn't be able to use indexes as flexibly. For example if
> you have an index on an int2 column and perform a query with a restriction
> like "int2col = 1" the system wouldn't find a matching =(int2,int4) operator
> and would instead have to do a sequential scan casting the int2 column to an
> int4 when checking each row.

This is a reason not to remove "redundant" indexable operators, but the
argument doesn't have a lot of force for non-indexable ones.

I looked into this, and the reason we fail to resolve int2 + int8 is
that after the "prefer more exact matches" test (the first heuristic in
func_select_candidate) we are down to int8 + int8 and int4 + int8,
and none of the remaining heuristics can prefer one over the other.
On the other hand, we resolve int2 < int8 just fine because there's an
exact match.

So it seems that the problem with cross-type operators is not so much
having them as having incomplete sets of them.  We could fix this case
either by adding int2 + int8 or by removing int4 + int8, and simplicity
would seem to argue for the latter.

A different approach would be to add a heuristic preferring
same-input-type operators over others.  (We currently apply that idea
only when one of the inputs is "unknown" type, which is why
'1' + int8'1' works.)  It's a bit scary to wonder what cases that might
break, though.

            regards, tom lane

Added to TODO:

* Add more cross-data-type operators

  http://archives.postgresql.org/pgsql-bugs/2008-01/msg00189.php


---------------------------------------------------------------------------

ykhuang wrote:
> there are many cross type arithmetic operators, like int2 + int4, int8 +
> int4, I think these can be deleted. Here are the reasons, after deleted,
> int2 + int4 will choose the operator int4 + int4, int8 + int4 choose int8 +
> int8, Is that ok? Thanks.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:
> Added to TODO:
> * Add more cross-data-type operators
>   http://archives.postgresql.org/pgsql-bugs/2008-01/msg00189.php

Uh ... that is exactly 180 degrees away from the point of the thread.

            regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Added to TODO:
> > * Add more cross-data-type operators
> >   http://archives.postgresql.org/pgsql-bugs/2008-01/msg00189.php
>
> Uh ... that is exactly 180 degrees away from the point of the thread.

OK, I see now, updated:

    * Simplify integer cross-data-type operators


Email text is:

    http://archives.postgresql.org/pgsql-bugs/2008-01/msg00199.php

    So it seems that the problem with cross-type operators is not so much
    having them as having incomplete sets of them.  We could fix this case
    either by adding int2 + int8 or by removing int4 + int8, and simplicity
    would seem to argue for the latter.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +