Re: v3proto Parse/Bind and the query planner - Mailing list pgsql-jdbc

Oliver,

I don't consider this a problem, but the expected behavior.  This is the
behavior I see on other databases (Oracle for example).  It is the price
you pay for parameterized queries.

--Barry


Oliver Jowett wrote:
> While doing some v3-related changes to the JDBC driver I ran into a bit
> of a problem with Parse/Bind.
>
> Using a parameterized Parse and then using Bind to pass the actual
> parameter values is a cleaner mapping from the JDBC PreparedStamement
> interface than directly substituting parameter values into the query
> itself. It also lets the driver pass the parameters in a binary form
> which is a big win for some parameter types e.g. bytea. And we don't
> have to play games with the parser to get a parameter type that reflects
> what was specified at the JDBC level.
>
> However it seems to interact badly with the query planner; where the
> planner would usually make a decision based on the selectivity of an
> index for a particular parameter value, it instead falls back to the
> more general case. For example, given this scenario:
>
>> create table test_big (value int);
>> insert into test_big values (1);
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big select * from test_big;
>> insert into test_big values (2);
>> create index test_big_index on test_big(value);
>> vacuum analyze verbose test_big;
>
>
> i.e. a large index where all but one row has the same value, I get these
> results:
>
> 1. SELECT count(*) FROM test_big WHERE value = 1: ~2500ms (seqscan)
> 2. SELECT count(*) FROM test_big WHERE value = 2: ~1.0ms (index scan)
>
> 3. SELECT count(*) FROM test_big WHERE value = $1, type of $1 is int4:
> 3a. Bind $1 to a text-format value "1": ~2500ms (as expected)
> 3b. Bind $1 to a text-format value "2": ~1450ms (looks like a seqscan!)
>
> For 3a/3b, the driver is sending this:
>
>>  FE=> Parse(name=_JDBC_1, query="SELECT count(*) FROM test_big WHERE
>> value = $1", oids={23})
>>  FE=> Bind(name=_JDBC_1, $1=1)
>>  FE=> Describe
>>  FE=> Execute(limit=0)
>>  FE=> ClosePortal
>>  FE=> Sync
>
>
> This is a bit of a barrier to using Parse/Bind by default. Ideally, I'd
> like a way to say "don't plan this query until you have actual parameter
> values" and have that turned on by default, but I can't find a way to do
> that in the v3 protocol.
>
> The existing strategy of doing parameter replacement on the driver side
> works, but we lose the benefits of passing parameters via Bind.
>
> Making the strategy configurable (per query? per connection?) is another
> possibility, but that means another postgresql-specific JDBC extension
> to tune depending on what your queries look like. We'd also have two
> separate code paths, which has caused problems in the past (e.g. the
> parameters may end up differently typed depending on which path is used).
>
> Any thoughts on how to handle this case?
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster