From: Robert Treat <xzilla@users.sourceforge.net>
To: pgsql-performance@postgresql.org
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Gregory Stark <stark@enterprisedb.com>
Cc: Csaba Nagy <nagy@ecircle-ag.com>
Cc: Bill Moran <wmoran@collaborativefusion.com>
Subject: Re: TB-sized databases
Date: Wed, 5 Dec 2007 15:07:21 -0500
Message-ID: <200712051507.22745.xzilla@users.sourceforge.net> (raw)
In-Reply-To: <1196352866.4246.1304.camel@ebony.site>
References: <4544e0330711260904g45d893a8ub1731e245aa8fc84@mail.gmail.com>
<13851.1196351131@sss.pgh.pa.us>
<1196352866.4246.1304.camel@ebony.site>
On Thursday 29 November 2007 11:14, Simon Riggs wrote:
> On Thu, 2007-11-29 at 10:45 -0500, Tom Lane wrote:
> > Gregory Stark <stark@enterprisedb.com> writes:
> > > "Simon Riggs" <simon@2ndquadrant.com> writes:
> > >> Tom's previous concerns were along the lines of "How would know what
> > >> to set it to?", given that the planner costs are mostly arbitrary
> > >> numbers.
> > >
> > > Hm, that's only kind of true.
> >
> > The units are not the problem. The problem is that you are staking
> > non-failure of your application on the planner's estimates being
> > pretty well in line with reality. Not merely in line enough that
> > it picks a reasonably cheap plan, but in line enough that if it
> > thinks plan A is 10x more expensive than plan B, then the actual
> > ratio is indeed somewhere near 10.
> >
> > Given that this list spends all day every day discussing cases where the
> > planner is wrong, I'd have to think that that's a bet I wouldn't take.
>
> I think you have a point, but the alternative is often much worse.
>
> If an SQL statement fails because of too high cost, we can investigate
> the problem and re-submit. If a website slows down because somebody
> allowed a very large query to execute then everybody is affected, not
> just the person who ran the bad query. Either way the guy that ran the
> query loses, but without constraints in place one guy can kill everybody
> else also.
>
> > You could probably avoid this risk by setting the cutoff at something
> > like 100 or 1000 times what you really want to tolerate, but how
> > useful is it then?
>
> Still fairly useful, as long as we understand its a blunt instrument.
>
> If the whole performance of your system depends upon indexed access then
> rogue queries can have disastrous, unpredictable consequences. Many
> sites construct their SQL dynamically, so a mistake in a seldom used
> code path can allow killer queries through. Even the best DBAs have been
> known to make mistakes.
>
If the whole performance of your system depends upon indexed access, then
maybe you need a database that gives you a way to force index access at the
query level?
> e.g. An 80GB table has 8 million blocks in it.
> - So putting a statement_cost limit = 1 million would allow some fairly
> large queries but prevent anything that did a SeqScan (or worse).
> - Setting it 10 million is going to prevent things like sorting the
> whole table without a LIMIT
> - Setting it at 100 million is going to prevent unconstrained product
> joins etc..
I think you're completly overlooking the effect of disk latency has on query
times. We run queries all the time that can vary from 4 hours to 12 hours in
time based solely on the amount of concurrent load on the system, even though
they always plan with the same cost.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: xzilla@users.sourceforge.net, simon@2ndquadrant.com, tgl@sss.pgh.pa.us, stark@enterprisedb.com, nagy@ecircle-ag.com, wmoran@collaborativefusion.com
Subject: Re: TB-sized databases
In-Reply-To: <200712051507.22745.xzilla@users.sourceforge.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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