From: | Amit Kapila <amit(dot)kapila(at)huawei(dot)com> |
---|---|
To: | "'Rodrigo Barboza'" <rodrigombufrj(at)gmail(dot)com> |
Cc: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Unrecognized type error (postgres 9.1.4) |
Date: | 2013-04-10 03:24:11 |
Message-ID: | 003301ce359a$df12f3c0$9d38db40$@kapila@huawei.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tuesday, April 09, 2013 6:19 PM Rodrigo Barboza wrote:
On Tue, Apr 9, 2013 at 3:05 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2013-04-10 04:04:17 | Re: Reassign variable value in XLogReadRecord |
Previous Message | Robert Haas | 2013-04-10 03:15:29 | bgworker sigusr1 handler |