Re: [PERFORM] statistics target for columns in unique constraint?

2013-05-20 Thread Kevin Grittner 
ach alanchi...@gmail.com wrote:

 One quick follow up since I'm expecting y'all might know:  Do the
 statistics targets actually speed performance on an index search
 itself; the actual lookup?  Or are the JUST to inform the planner
 towards the best pathway decision?

Since the statistics are just a random sampling and generally not
completely up-to-date, they really can't be used for anything other
than *estimating* relative costs in order to try to pick the best
plan.  Once a plan is chosen, its execution time is not influenced
by the statistics.  A higher statistics target can increase
planning time.  In a complex query with many joins and many indexes
on the referenced tables, the increase in planning time can be
significant.  I have seen cases where blindly increasing the
default statistics target resulted in planning time which was
longer than run time -- without any increase in plan quality.

Generally when something is configurable, it's because there can be
benefit to adjusting it.  If there was a single setting which could
not be materially improved upon for some cases, we wouldn't expose
a configuration option.  This is something which is not only
globally adjustable, you can override the setting for individual
columns -- again, we don't go to the trouble of supporting that
without a good reason.

-- 
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

2013-05-20 Thread Tomas Vondra 
On 20.5.2013 05:00, Greg Smith wrote:
 On 5/16/13 8:06 PM, Tomas Vondra wrote:
 Have you considered using a UPS? That would make the SSDs about as
 reliable as SATA/SAS drives - the UPS may fail, but so may a BBU unit on
 the SAS controller.
 
 That's not true at all.  Any decent RAID controller will have an option
 to stop write-back caching when the battery is bad.  Things will slow
 badly when that happens, but there is zero data risk from a short-term
 BBU failure.  The only serious risk with a good BBU setup are that
 you'll have a power failure lasting so long that the battery runs down
 before the cache can be flushed to disk.

That's true, no doubt about that. What I was trying to say is that a
controller with BBU (or a SSD with proper write cache protection) is
about as safe as an UPS when it comes to power outages. Assuming both
are properly configured / watched / checked.

Sure, there are scenarios where UPS is not going to help (e.g. a PSU
failure) so a controller with BBU is better from this point of view.
I've seen crashes with both options (BBU / UPS), both because of
misconfiguration and hw issues. BTW I don't know what controller are we
talking about here - it might be as crappy as the SSD drives.

What I was thinking about in this case is using two SSD-based systems
with UPSes. That'd allow fast failover (which may not be possible with
the SAS based replica, as it does not handle the load).

But yes, I do agree that the provider should be ashamed for not
providing reliable SSDs in the first place. Getting reliable SSDs should
be the first option - all these suggestions are really just workarounds
of this rather simple issue.

Tomas


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance