Skip site navigation (1) Skip section navigation (2)

Re: Prepared Statements: Inefficient Type Conversion?

From: Kris Jurka <books(at)ejurka(dot)com>
To: James House <jhouse(at)part(dot)net>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Prepared Statements: Inefficient Type Conversion?
Date: 2007-04-16 19:29:27
Message-ID: Pine.BSO.4.64.0704161525360.6935@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

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

In response to

Responses

pgsql-jdbc by date

Next:From: James HouseDate: 2007-04-16 19:33:34
Subject: Re: Prepared Statements: Inefficient Type Conversion?
Previous:From: Ville JantunenDate: 2007-04-16 19:26:18
Subject: Re: Bug in timezone-parsing?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group