Skip site navigation (1) Skip section navigation (2)

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

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: Kenneth Marshall <ktm(at)rice(dot)edu>
Cc: Greg Smith <greg(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Bruce Momjian <bruce(at)momjian(dot)us>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "david(at)lang(dot)hm" <david(at)lang(dot)hm>, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, Vitalii Tymchyshyn <tivv00(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: [HACKERS] Slow count(*) again...
Date: 2011-02-02 21:59:50
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-performance
Kenneth Marshall wrote:
> I see them come up regularly. However, there really are not all that
> many when you consider how many people are using PostgreSQL. Its
> optimizer works quite well. Knowing how hints can be misused, I would
> rather have the developers use their resource to improve the optimizer
> than spend time on a hint system that would be mis-used over and over
> by beginners, with the attendent posts to HACKERS/PERFORM/NOVICE/...
> groups. I certainly have had a fun time or two in my limited Oracle
> experience tracking down a hint-based performance problem, so it
> works both ways.
> Regards,
> Ken

Ken, the story is really simple: when a problem with a bad query arises, 
the DBA has to make it work, one way or another.  The weapon of choice 
are usually hints, but there is also the ability to set the critical 
statistic variables to the desired values. If my users are screaming 
that the application response time is slow, I cannot afford to wait for 
developers to fix the optimizer. I will therefore not use Postgres for 
my mission critical applications, as long as there are no hints.

Oracle is expensive, but not as expensive as the downtime. And that's 
the bottom line. Yes, hints can cause problems, but the absence of hints 
and wait interface can cause even bigger problems. This is not a choice 
between good and evil, as in the Nick Cage movies, it is a choice 
between evil and lesser evil. I would love to be able  to use Postgres 
for some of my mission critical applications. Saving tens of thousands 
of dollars would make me a company hero and earn me a hefty bonus, so I 
have a personal incentive to do so. Performance is normally not a 
problem. If the application is carefully crafted and designed, it will 
work more or less the same as Oracle. However, applications sometimes 
need maintenance. Ruth from sales wants the IT to start ingesting data 
in UTF8 because we have clients in other countries. She also wants us to 
track language and countries. Columns have to be added to the tables, 
applications have to be changed, foreign keys added, triggers altered, 
etc, etc.  What you end up with is usually less than optimal. 
Applications have life cycle and they move from being young and sexy to 
being an old fart application, just as people do.  Hints are Viagra for 
applications. Under the ideal conditions, it is not needed, but once the 
app is past certain age....

The other problem is that plans change with the stats, not necessarily 
for the better. People clean a large table, Postgres runs auto-vacuum, 
stats change and all the plans change, too. If some of the new plans are 
unacceptable, there isn't much you can do about it, but to hint it to 
the proper plan. Let's not pretend, Postgres does support sort of hints 
with the "set enable_<access method>" and random/sequential scan cost. 
Also, effective cache size is openly used to trick the optimizer into 
believing that there is more memory than there actually is. Hints are 
already there, they're just not as elegant as Oracle's solution. If I 
set sequential page cost to 4 and random page cost to 1, I have, 
effectively, introduced rule based optimizer to Postgres.  I am not sure 
why is there such a puritanical resistance to hints on one side and, on 
other side, there are means to achieve exactly the same thing.  As my 
signature line says, I am a senior Oracle DBA, with quite a bit  of 
experience. What I need to approve moving mission critical applications 
to Postgres are better monitoring tools and something to help me with 
quick and dirty fixes when necessary. I am willing to learn, I got the 
company to invest some money and do pilot projects, but I am not 
prepared to have my boss saying "we could have fixed the problem, had we 
stayed on Oracle".

On my last airplane trip, I saw Nick Cage in the "Sorcerer's Apprentice" 
and my brain still hurts.

Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251 
The Leader in Integrated Media Intelligence Solutions

In response to

pgsql-performance by date

Next:From: Mladen GogalaDate: 2011-02-02 22:03:28
Subject: Re: [HACKERS] Slow count(*) again...
Previous:From: Justin PittsDate: 2011-02-02 21:25:00
Subject: Re: [HACKERS] Slow count(*) again...

pgsql-hackers by date

Next:From: Mladen GogalaDate: 2011-02-02 22:03:28
Subject: Re: [HACKERS] Slow count(*) again...
Previous:From: Kevin GrittnerDate: 2011-02-02 21:42:54
Subject: Re: SSI patch version 14

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group