pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPrepared statements slow in 9.2 still (bad query plan)
4+ messages / 3 participants
[nested] [flat]
* Prepared statements slow in 9.2 still (bad query plan)
@ 2012-10-23 15:42 Daniel Burbridge <Daniel.Burbridge@publishingtechnology.com>
2012-10-28 15:06 ` Re: Prepared statements slow in 9.2 still (bad query plan) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Daniel Burbridge @ 2012-10-23 15:42 UTC (permalink / raw)
To: pgsql-performance
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/conten...;)
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Prepared statements slow in 9.2 still (bad query plan)
2012-10-23 15:42 Prepared statements slow in 9.2 still (bad query plan) Daniel Burbridge <Daniel.Burbridge@publishingtechnology.com>
@ 2012-10-28 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-10-29 13:25 ` Re: Prepared statements slow in 9.2 still (bad query plan) Shaun Thomas <sthomas@optionshouse.com>
2012-10-30 16:41 ` Re: Prepared statements slow in 9.2 still (bad query plan) Daniel Burbridge <Daniel.Burbridge@publishingtechnology.com>
0 siblings, 2 replies; 4+ messages in thread
From: Tom Lane @ 2012-10-28 15:06 UTC (permalink / raw)
To: Daniel Burbridge <Daniel.Burbridge@publishingtechnology.com>; +Cc: pgsql-performance
Daniel Burbridge <Daniel.Burbridge@publishingtechnology.com> writes:
> I have a problem with prepared statements choosing a bad query plan - I was hoping that 9.2 would have eradicated the problem :(
9.2 will only pick the "right" plan if that plan's estimated cost is a
good bit cheaper than the "wrong" parameterized plan. In this case,
not only is there not a lot of difference, but the difference is in the
wrong direction. You need to fix that --- perhaps increasing stats
targets would help?
A more radical question is whether you have a well-chosen set of indexes
in the first place. These two seem a bit odd, and certainly not
terribly well matched to this query.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Prepared statements slow in 9.2 still (bad query plan)
2012-10-23 15:42 Prepared statements slow in 9.2 still (bad query plan) Daniel Burbridge <Daniel.Burbridge@publishingtechnology.com>
2012-10-28 15:06 ` Re: Prepared statements slow in 9.2 still (bad query plan) Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-10-29 13:25 ` Shaun Thomas <sthomas@optionshouse.com>
1 sibling, 0 replies; 4+ messages in thread
From: Shaun Thomas @ 2012-10-29 13:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Daniel Burbridge <Daniel.Burbridge@publishingtechnology.com>; pgsql-performance
On 10/28/2012 10:06 AM, Tom Lane wrote:
> 9.2 will only pick the "right" plan if that plan's estimated cost is a
> good bit cheaper than the "wrong" parameterized plan.
Is it also possible that the planner differences between extended and
simple query mode caused this? That really bit us in the ass until
EnterpriseDB sent us a patch. From browsing the threads, didn't someone
say a similar problem existed in PG core?
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Prepared statements slow in 9.2 still (bad query plan)
2012-10-23 15:42 Prepared statements slow in 9.2 still (bad query plan) Daniel Burbridge <Daniel.Burbridge@publishingtechnology.com>
2012-10-28 15:06 ` Re: Prepared statements slow in 9.2 still (bad query plan) Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-10-30 16:41 ` Daniel Burbridge <Daniel.Burbridge@publishingtechnology.com>
1 sibling, 0 replies; 4+ messages in thread
From: Daniel Burbridge @ 2012-10-30 16:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Thanks,
default_statistics_target is currently at 500 (I have tried from 100-5000 without any success)
Would upping the stats for one specific column help? If so, I presume I should up the stats on the subj column...
You may well be onto something wrt the indexes and their usage - this is a not a system that I have built but as is often the case been asked to look at the performance of....
It is an RDB triplestore for Apache-Jena with approx 17 million triples/rows.
There are only 4 columns - subj,prop,obj and graphid (which in our case is always 1)
According to the stats that have been collected subj has approx 350,000 distinct values, prop 88 and obj around 150,000
Dan
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 28 October 2012 15:06
To: Daniel Burbridge
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Prepared statements slow in 9.2 still (bad query plan)
Daniel Burbridge writes:
> I have a problem with prepared statements choosing a bad query plan -
> I was hoping that 9.2 would have eradicated the problem :(
9.2 will only pick the "right" plan if that plan's estimated cost is a good bit cheaper than the "wrong" parameterized plan. In this case, not only is there not a lot of difference, but the difference is in the wrong direction. You need to fix that --- perhaps increasing stats targets would help?
A more radical question is whether you have a well-chosen set of indexes in the first place. These two seem a bit odd, and certainly not terribly well matched to this query.
regards, tom lane
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-10-30 16:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-23 15:42 Prepared statements slow in 9.2 still (bad query plan) Daniel Burbridge <Daniel.Burbridge@publishingtechnology.com>
2012-10-28 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-10-29 13:25 ` Shaun Thomas <sthomas@optionshouse.com>
2012-10-30 16:41 ` Daniel Burbridge <Daniel.Burbridge@publishingtechnology.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox