v3proto Parse/Bind and the query planner - Mailing list pgsql-jdbc
From | Oliver Jowett |
---|---|
Subject | v3proto Parse/Bind and the query planner |
Date | |
Msg-id | 40A84236.6070809@opencloud.com Whole thread Raw |
Responses |
Re: v3proto Parse/Bind and the query planner
Re: v3proto Parse/Bind and the query planner |
List | pgsql-jdbc |
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
pgsql-jdbc by date: