[GENERAL] How are pg_operator and pg_type related with each other?
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!
Re: [GENERAL] How are pg_operator and pg_type related with each other?
On Wed, Jan 15, 2014 at 11:08 PM, Felix.徐 ygnhz...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How are pg_operator and pg_type related with each other?
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 amitlangot...@gmail.com On Wed, Jan 15, 2014 at 11:08 PM, Felix.徐 ygnhz...@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
Re: [GENERAL] How are pg_operator and pg_type related with each other?
=?GB2312?B?RmVsaXgu0Ow=?= ygnhz...@gmail.com writes: I'm wondering where I can find the corresponding operatorID of eq/lt for a specific data type. The ones ANALYZE uses are the members of the default btree opclass for the datatype. If there isn't one, it doesn't build a histogram. 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? I don't believe that's possible from SQL; as you found out, the type system won't allow it, and it'd be a security hole if it did (since there wouldn't be any check that the data you store actually matches the type of the column the pg_statistic row claims to be about). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general