Re: Prepared Statements: Inefficient Type Conversion?

On Mon, 16 Apr 2007, James House wrote:

>> You can try adding the url parameter stringtype=unspecified which will pass
>> the String parameter untyped instead of as a String.
>
> FWIW, that didn't seem to make a difference - actually, it takes even longer
> to execute.
>

That's odd I was expecting to see something like this:

jurka=# CREATE TABLE test (a numeric);
CREATE TABLE
jurka=# CREATE INDEX test_idx_a ON test(a);
CREATE INDEX
jurka=# EXPLAIN SELECT * FROM test WHERE a = '1'::text;
                       QUERY PLAN
------------------------------------------------------
  Seq Scan on test  (cost=0.00..29.65 rows=7 width=32)
    Filter: ((a)::text = '1'::text)
(2 rows)

Get changed to something like this:

jurka=# EXPLAIN SELECT * FROM test WHERE a = '1'::unknown;
                                QUERY PLAN
-------------------------------------------------------------------------
  Bitmap Heap Scan on test  (cost=4.30..14.45 rows=7 width=32)
    Recheck Cond: (a = 1::numeric)
    ->  Bitmap Index Scan on test_idx_a  (cost=0.00..4.30 rows=7 width=0)
          Index Cond: (a = 1::numeric)
(4 rows)

Perhaps you have a JDBC driver that is too old to understand the
stringtype URL parameter?  Looks like it is only available in 8.2 and on.
So if you don't have an 8.2 driver it won't do anything.

Kris Jurka