Postgres Pro
Downloads
Home   >   mailing lists

Re: How are pg_operator and pg_type related with each other? - Mailing list pgsql-general

From Felix.徐
Subject Re: How are pg_operator and pg_type related with each other?
Date
Msg-id CAPmhLM3J86HtXkqgaVO0Oq+7m=Lszjq19teEvrOxeAE55nMXAA@mail.gmail.com
Whole thread Raw
In response to Re: How are pg_operator and pg_type related with each other?  (Amit Langote <amitlangote09@gmail.com>)
Responses Re: How are pg_operator and pg_type related with each other?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tree view
Hi Amit
I understand, I've read the source code of analyze.c and implemented a java version.
Stakind1(most common values) indicates "=" operator and stakind2(histogram) indicates "<" operator by default, 
I'm wondering where I can find the corresponding operatorID of eq/lt for a specific data type.
For example,
"=" operator for the type "varchar" is "texteq" and "<" operator for varchar is "text_lt"
"=" operator for the type "int4" is "int4eq" and "<" operator for int4 is "int4lt"  etc.

And another question is that how to update or insert a column with type of "anyarray", since I want to mock the statistic data of tables, the type of stavalues in pg_statistic is anyarray, is there any way to manually modify that column, by some kind of function or hook?
If I ran the query like:

UPDATE pg_statistic
   SET stavalues2=array[1,2,8]
 WHERE ...

Then I will get error 42804 indicates that the expected type is anyarry but text[] is found in the query.


Thanks very much!



2014/1/15 Amit Langote <amitlangote09@gmail.com>
On Wed, Jan 15, 2014 at 11:08 PM, Felix.徐 <ygnhzeus@gmail.com> wrote:
> Hi all,
> I'm studying pg_statistic table and I find that column staop is related to
> pg_operator, and different data types relate to different staop, but I don't
> know where pgsql stores the mapping between pg_type and pg_operator, does
> anyone have any idea about it? thanks!

Rather, different "kinds" of statistics are related to different
operators. So, "staop2" would refer to an operator suitable/applicable
for the statistics of kind "stakind2".

For example stakind2 for some attribute could be value "3" which
refers to statistic kind "histogram". In this case, staop2 for the
same attribute could refer to operator, say, "<" because this
particular operator could benefit from histogram distribution of
values. (off course, "<" may be overloaded for different types; but
point to take from this is that any "<" uses the statistic called
histogram.)

--
Amit Langote

pgsql-general by date:

Previous
From: itishree sukla
Date:
Subject: Need Help to implement Proximity search feature
Next
From: Vick Khera
Date:
Subject: Re: how is text-equality handled in postgresql?