|
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. |
