I have a problem with prepared statements choosing a bad query plan - I was
hoping that 9.2 would have eradicated the problem :(
Taken from the postgresql log:
<2012-10-23 15:21:03 UTC acme_metastore 13798
5086b49e.35e6> LOG: duration: 20513.809 ms execute S_6: SELECT S.Subj,
S.Prop, S.Obj
FROM
jena_g1t1_stmt S WHERE S.Obj = $1 AND S.Subj = $2 AND S.Prop = $3 AND S.GraphID
= $4
<2012-10-23 15:21:03 UTC acme_metastore 13798
5086b49e.35e6> DETAIL: parameters: $1 =
'Uv::http://www.w3.org/2006/vcard/ns#Organization', $2 =
'Uv::http://acme.metastore.acmeemca.com/content/journals/10.1049/acme-ipr.2010.0367-af2-org',
$3 = 'Uv::http://www.w3.org/1999/02/22-rdf-syntax-ns#type', $4 = '1'
<2012-10-23 15:21:03 UTC acme_metastore 13798
5086b49e.35e6> LOG: duration: 20513.790 ms plan:
Query Text:
SELECT S.Subj, S.Prop, S.Obj
FROM
jena_g1t1_stmt S WHERE S.Obj = $1 AND S.Subj = $2 AND S.Prop = $3 AND S.GraphID
= $4
Index Scan
using jena_g1t1_stmt_ixpo on jena_g1t1_stmt s (cost=0.00..134.32 rows=1
width=183)
Index Cond:
(((prop)::text = ($3)::text) AND ((obj)::text = ($1)::text))
Filter:
(((subj)::text = ($2)::text) AND (graphid = $4))
The same query written in line: as you can see its using a different index and
is therefore orders of magnitude quicker.
SELECT S.Subj, S.Prop, S.Obj
FROM
jena_g1t1_stmt S WHERE S.Obj =
'Uv::http://www.w3.org/2006/vcard/ns#Organization' AND S.Subj =
'Uv::http://acme.metastore.acmeemca.com/content/journals/10.1049/acme-ipr.2010.0367-af2-org'
AND S.Prop = 'Uv::http://www.w3.org/1999/02/22-rdf-syntax-ns#type' AND
S.GraphID = '1';
Index Scan
using jena_g1t1_stmt_ixsp on jena_g1t1_stmt s (cost=0.00..168.64 rows=1
width=183) (actual time=0.181..0.183 rows=1 loops=1)
Index Cond: (((subj)::text =
'Uv::http://acme.metastore.acmeemca.com/content/journals/10.1049/acme-ipr.2010.0367-af2-org'::text)
AND ((prop)::text =
'Uv::http://www.w3.org/1999/02/22-rdf-syntax-ns#type'::text))
Filter: (((obj)::text =
'Uv::http://www.w3.org/2006/vcard/ns#Organization'::text) AND (graphid = 1))
Total runtime: 0.268 ms
(4 rows)
If I write it as a prepared statement in psql it also now chooses the correct
index (in v9.1 it would pick the wrong one)
prepare testplan as SELECT S.Subj, S.Prop, S.Obj
FROM
jena_g1t1_stmt S WHERE S.Obj = $1 AND S.Subj = $2 AND S.Prop = $3 AND S.GraphID
= $4;
explain analyze
execute testplan
('Uv::http://www.w3.org/2006/vcard/ns#Organization','Uv::http://acme.metastore.acmeemca.com/content/journals/10.1049/acme-ipr.2010.0367-af2-org','Uv::http://www.w3.org/1999/02/22-rdf-syntax-ns#type','1')
Index Scan
using jena_g1t1_stmt_ixsp on jena_g1t1_stmt s (cost=0.00..168.64 rows=1
width=183) (actual time=0.276..0.278 rows=1 loops=1)
Index Cond: (((subj)::text =
'Uv::http://acme.metastore.acmeemca.com/content/journals/10.1049/acme-ipr.2010.0367-af2-org'::text)
AND ((prop)::text =
'Uv::http://www.w3.org/1999/02/22-rdf-syntax-ns#type'::text))
Filter: (((obj)::text =
'Uv::http://www.w3.org/2006/vcard/ns#Organization'::text) AND (graphid = 1))
Total runtime: 0.310 ms
(4 rows)
The queries are generated by Apache Jena / sparql. I have tried adding
?protocolVersion=2 to the jbdc connection string - but I still see the queries
as prepared statements.
>From the wiki:
"Prepared statements used to be optimized once, without any knowledge of the
parameters' values. With 9.2, the planner will use specific plans regarding to
the parameters sent (the query will be planned at execution), except if the
query is executed several times and the planner decides that the generic plan
is not too much more expensive than the specific plans."
Is there a way to force the planner to use the specific rather than generic
plans?
Dan
The information in this message is intended solely for the addressee and should
be considered confidential. Publishing Technology does not accept legal
responsibility for the contents of this message and any statements contained
herein which do not relate to the official business of Publishing Technology
are neither given nor endorsed by Publishing Technology and are those of the
individual and not of Publishing Technology. This message has been scanned for
viruses using the most current and reliable tools available and Publishing
Technology excludes all liability related to any viruses that might exist in
any attachment or which may have been acquired in transit.
