Re: Prepared statements slow in 9.2 still (bad query plan)

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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 28 October 2012 15:06
To: Daniel Burbridge
Cc: pgsql-performance(at)postgresql(dot)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.