Re: [HACKERS] Slow count(*) again...

On Thu, 3 Feb 2011, Robert Haas wrote:

> On Thu, Feb 3, 2011 at 7:39 PM, <david(at)lang(dot)hm> wrote:
>>> Yeah, but you'll be passing the entire table through this separate
>>> process that may only need to see 1% of it or less on a large table.
>>> If you want to write the code and prove it's better than what we have
>>> now, or some other approach that someone else may implement in the
>>> meantime, hey, this is an open source project, and I like improvements
>>> as much as the next guy.  But my prediction for what it's worth is
>>> that the results will suck.  :-)
>>
>> I will point out that 1% of a very large table can still be a lot of disk
>> I/O that is avoided (especially if it's random I/O that's avoided)
>
> Sure, but I think that trying to avoid it will be costly in other ways
> - you'll be streaming a huge volume of data through some auxiliary
> process, which will have to apply some algorithm that's very different
> from the one we use today. The reality is that I think there's little
> evidence that the way we do ANALYZE now is too expensive. It's
> typically very cheap and works very well. It's a bit annoying when it
> fires off in the middle of a giant data load, so we might need to
> change the time of it a little, but if there's a problem with the
> operation itself being too costly, this is the first I'm hearing of
> it. We've actually worked *really* hard to make it cheap.

I could be misunderstanding things here, but my understanding is that it's
'cheap' in that it has little impact on the database while it is running.

the issue here is that the workflow is

load data
analyze
start work

so the cost of analyze in this workflow is not "1% impact on query speed
for the next X time", it's "the database can't be used for the next X time
while we wait for analyze to finish running"

I don't understand why the algorithm would have to be so different than
what's done today, surely the analyze thread could easily be tweaked to
ignore the rest of the data (assuming we don't have the thread sending the
data to analyze do the filtering)

David Lang
>From pgsql-performance-owner(at)postgresql(dot)org Thu Feb 3 21:46:39 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
by mail.postgresql.org (Postfix) with ESMTP id 7F1811337B96
for <pgsql-performance-postgresql(dot)org(at)mail(dot)postgresql(dot)org>; Thu, 3 Feb 2011 21:46:39 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024)
with ESMTP id 80837-04
for <pgsql-performance-postgresql(dot)org(at)mail(dot)postgresql(dot)org>;
Fri, 4 Feb 2011 01:46:32 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from outmail148143.authsmtp.com (outmail148143.authsmtp.com [62.13.148.143])
by mail.postgresql.org (Postfix) with ESMTP id AF4A11337B95
for <pgsql-performance(at)postgresql(dot)org>; Thu, 3 Feb 2011 21:46:31 -0400 (AST)
Received: from mail-c193.authsmtp.com (mail-c193.authsmtp.com [62.13.128.118])
by punt8.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id p141kVx4097555;
Fri, 4 Feb 2011 01:46:31 GMT
Received: from Sidney-Stratton.local (dsl081-245-111.sfo1.dsl.speakeasy.net [64.81.245.111])
(authenticated bits=0)
by mail.authsmtp.com (8.14.2/8.14.2) with ESMTP id p141kSnH064206;
Fri, 4 Feb 2011 01:46:29 GMT
Message-ID: <4D4B5A73(dot)3080302(at)agliodbs(dot)com>
Date: Thu, 03 Feb 2011 17:46:27 -0800
From: Josh Berkus <josh(at)agliodbs(dot)com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2
MIME-Version: 1.0
To: pgsql-performance(at)postgresql(dot)org
CC: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
Subject: Re: Why we don't want hints Was: Slow count(*) again...
References: <201102012247(dot)p11Ml6u02682(at)momjian(dot)us> <4D48914B(dot)4050403(at)dunslane(dot)net> <4D489AD1(dot)1010105(at)vmsinfo(dot)com> <AANLkTiknZi97dDwm7sHqy0EL7tpNEP9sY5uPV6AN+q=u(at)mail(dot)gmail(dot)com> <4D499E55(dot)4020107(at)vmsinfo(dot)com> <4D49A6B9(dot)4060608(at)2ndquadrant(dot)com> <4D49C482(dot)4010707(at)vmsinfo(dot)com> <4D49F0BA(dot)9000901(at)2ndquadrant(dot)com> <4D4A16BF(dot)1020304(at)vmsinfo(dot)com> <4D4A4844(dot)3020409(at)2ndquadrant(dot)com> <4D4AD9F6(dot)6040903(at)vmsinfo(dot)com> <4D4AE16E(dot)9040807(at)peak6(dot)com> <4D4B17B4(dot)9030406(at)vmsinfo(dot)com> <4D4B1F5B(dot)4060403(at)peak6(dot)com> <4D4B261B(dot)6010303(at)vmsinfo(dot)com> <AANLkTi=6_7N4EuT-trfPbxb9UmanJsnL+ineO-iJUFtJ(at)mail(dot)gmail(dot)com> <4D4B2E8A(dot)7010402(at)vmsinfo(dot)com> <4D4B3395(dot)6010007(at)agliodbs(dot)com> <4D4B394E(dot)9080505(at)vmsinfo(dot)com>
In-Reply-To: <4D4B394E(dot)9080505(at)vmsinfo(dot)com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Server-Quench: 9647b83a-3000-11e0-97bb-002264978518
X-AuthReport-Spam: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse
X-AuthRoute: OCdyZgscClZXSx8a IioLCC5HRQ8+YBZL BAkGMA9GIUINWEQL c1ACch19PVdbHwkA AnYLWl5QVldyWS1z bxRZbBtfZk9QXgRr T0pMQFdNFEsoABgA XX1AKhl0cwdGfjB3 Zk9qEHldWEMofUUs X01UFW0bZGY1aH0W VxIKagNUcgFMehZC YlV+XD1vNG8XDRoV JSEUBRUEdQpfOWxK T0kBKlRdXQ4UFzgg DxADGyk0VXIMXHd7 FBghNRYXG1sXLgVw cBMoVlsZNVlUTGUA
X-Authentic-SMTP: 61633136333939.1014:706
X-AuthFastPath: 0 (Was 255)
X-AuthVirus-Status: No virus detected - but ensure you scan with your own anti-virus system.
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level:
X-Archive-Number: 201102/149
X-Sequence-Number: 42287

> http://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want
>
> No. 2 on the list.

Heck, *I* wrote that text.

I quote:

"Optimizer hints are used to work around problems in the optimizer and
introduce upgrade and maintenance issues. We would rather have the
problems reported and fixed. We have discussed a more sophisticated
system of per-class cost adjustment instead, but a specification remains
to be developed."

That seems pretty straightforwards. There are even links to prior
discussions about what kind of system would work. I don't think this
text needs any adjustment; that's our clear consensus on the hint issue:
we want a tool which works better than what we've seen in other databases.

Quite frankly, the main reason why most DBMSes have a hinting system has
nothing to do with the quality of optimizer and everything to do with
DBAs who think they're smarter than the optimizer (incorrectly). Oracle
has a darned good query optimizer, and SQL server's is even better.
However, there are a lot of undereducated or fossilized DBAs out there
who don't trust the query planner and want to override it in fairly
arbitrary ways; I refer you to the collected works of Dan Tow, for example.

In many cases Hints are used by DBAs in "emergency" situations because
they are easier than figuring out what the underlying issue is, even
when that could be done relatively simply. Improving diagnostic query
tools would be a much better approach here; for example, the team
working on hypothetical indexes has a lot to offer. If you can figure
out what's really wrong with the query in 10 minutes, you don't need a hint.

Yes, I occasionally run across cases where having a query tweaking
system would help me fix a pathological failure in the planner.
However, even on data warehouses that's less than 0.1% of the queries I
deal with, so this isn't exactly a common event. And any hinting system
we develop needs to address those specific cases, NOT a hypothetical
case which can't be tested. Otherwise we'll implement hints which
actually don't improve queries.

Commercial DBMSes have to give in to what their big paying customers
want, no matter how stupid it is. I'm grateful that I can work on a DBMS
-- the third most popular SQL DBMS in the world -- which can focus on
quality instead.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com