Обсуждение: Prepared statements slow in 9.2 still (bad query plan)

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