On Tuesday, April 09, 2013 6:19 PM Rodrigo Barboza wrote: On Tue, Apr 9, 2013 at 3:05 AM, Amit Kapila <amit.kap...@huawei.com> wrote: On Monday, April 08, 2013 7:28 PM Rodrigo Barboza wrote: >> You have identified rightly in your other mail that it happens in function >> convert_numeric_to_scalar(). But I think adding user defined datatype >> handling in this function might >> not be straight forward. You can refer below text from link >> http://www.postgresql.org/docs/9.2/static/xoper-optimization.html >> "You can use scalarltsel and scalargtsel for comparisons on data types that >> have some sensible means of being converted into numeric scalars for range >> comparisons. If possible, add the data type to those understood by the >> function convert_to_scalar() in src/backend/utils/adt/selfuncs.c. >> (Eventually, this function should be replaced by per-data-type functions >> identified through a column of the pg_type system catalog; but that hasn't >> happened yet.) If you do not do this, things will still work, but the >> optimizer's estimates won't be as good as they could be."
>> I could think of following workaround's for your problem. >> 1. For your table, set values for autovacuum_analyze_threshold and >> autovacuum_analyze_scale_factor very high (refer Create Table), so that it >> doesn't analyze your >> table and return default selectivity, which should work fine if your sql >> statements are simple. >> 2. Write your own selectivity functions and return default Selectivity from >> them and use them while creating operators. >> 3. Use bind value in where clause, it will return default selectivity for >> it. >> 4. Some other way, with which it does not collect histogram stats (means it >> will use minimal stats compute_minimal_stats). I am not sure but you can try >> once without defining operators. >> All the above way's can help to resolve your current problem, but they are >> not good way if you have some usage of sql statements with these datatypes. > Hi, Amit, thank you for your reply. > The text says: "if you do not do this, things will still work, but the optimizer's estimates won't be as good as they could be.". > But this is not what is happening, he is raising unsupported type error. > I think option 1 and 4 is not for me. > Option 2 could be a solution, but I don't know how to start writing this kind of function. Do you any tips? You need to write C function and use them while creating operator's, Refer link: http://www.postgresql.org/docs/9.2/static/xfunc-c.html > I didn't understand option 3, what did you mean? Option 3 means you can use prepared statements to specify bind values for where clause. Refer link: http://www.postgresql.org/docs/9.2/static/sql-prepare.html With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
