X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 46624D8FA0 for ; Sun, 23 Oct 2005 18:35:38 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 70442-09 for ; Sun, 23 Oct 2005 21:35:35 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.206]) by svr1.postgresql.org (Postfix) with ESMTP id 4208BD8978 for ; Sun, 23 Oct 2005 18:35:36 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id 8so503930nzo for ; Sun, 23 Oct 2005 14:35:38 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=eEQPZO56gmmGxIh58QFugliss9a1RtASPZVoOBNKSM38ii7UOVqFyEcxR200fHw7P754Ur0aJsrvKbDIuryZiJr6pcc/+snF3VMd/8zXpF7nmkRq0YBKJDoVK7Ghfx784Mi5ofGybcWYRbSESPhiE06abUwSQZt4MNbp+642vWY= Received: by 10.37.20.35 with SMTP id x35mr5214365nzi; Sun, 23 Oct 2005 14:35:38 -0700 (PDT) Received: by 10.36.61.1 with HTTP; Sun, 23 Oct 2005 14:35:38 -0700 (PDT) Message-ID: <42567e060510231435g113273edidc97207a71abf5f@mail.gmail.com> Date: Mon, 24 Oct 2005 03:05:38 +0530 From: Kishore B To: pgsql-performance@postgresql.org Subject: Re: Need help in setting optimal configuration for a huge database. In-Reply-To: <435BBAF0.8080500@modgraph-usa.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_26465_15359058.1130103338838" References: <42567e060510221415r2516523l4c3f9b42dee91a09@mail.gmail.com> <435BBAF0.8080500@modgraph-usa.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.455 required=5 tests=[AWL=-0.074, HTML_20_30=0.504, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200510/320 X-Sequence-Number: 15088 ------=_Part_26465_15359058.1130103338838 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Craig, Thank you very much for your response. It really covered a great point. Thank you, Kishore. On 10/23/05, Craig A. James wrote: > > > > We are facing a* critical situation because of the performance of the > > **database** .* Even a basic query like select count(*) from > > bigger_table is taking about 4 minutes to return. > > Several other replies have mentioned that COUNT() requires a full table > scan, but this point can't be emphasized enough: Don't do it! People who = are > migrating from other environments (Oracle or MySQL) are used to COUNT(), > MIN() and MAX() returning almost instantaneously, certainly on indexed > columns. But for reasons that have something to do with transactions, the= se > operations are unbelievably slow in PostgreSQL. > > Here are the alternatives that I've learned. > > COUNT() -- There is no good substitute. What I do is create a new column, > "ROW_NUM" with an auto-incrementing sequence. Every time I insert a row, = it > gets a new value. Unfortunately, this doesn't work if you ever delete a r= ow. > The alternative is a more complex pair of triggers, one for insert and on= e > for delete, that maintains the count in a separate one-row table. It's a > nuisance, but it's a lot faster than doing a full table scan for every > COUNT(). > > MIN() and MAX() -- These are surprisingly slow, because they seem to do a > full table scan EVEN ON AN INDEXED COLUMN! I don't understand why, but > happily there is an effective substitute: > > select mycolumn from mytable order by mycolumn limit 1; -- same as MIN() > > select mycolumn from mytable order by mycolumn desc limit 1; -- same as > MAX() > > For a large table, MIN or MAX can take 5-10 minutes, where the above > "select..." replacements can return in one millisecond. > > You should carefully examine your entire application for COUNT, MIN, and > MAX, and get rid of them EVERYWHERE. This may be the entire source of you= r > problem. It was in my case. > > This is, in my humble opinion, the only serious flaw in PostgreSQL. I've > been totally happy with it in every other way, and once I understood thes= e > shortcomings, my application is runs faster than ever on PostgreSQL. > > Craig > ------=_Part_26465_15359058.1130103338838 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi Craig,
 
Thank you very much for your response.
 
It really covered a great point.
 
Thank you,
Kishore.

 
On 10/23/05, Craig A. James <cjames@m= odgraph-usa.com> wrote:

> We are facing a* critic= al situation because of the performance of the
> **database** .* Even= a basic query like select count(*) from
> bigger_table is taking about 4 minutes to return.

Several o= ther replies have mentioned that COUNT() requires a full table scan, but th= is point can't be emphasized enough: Don't do it!  People who are= migrating from other environments (Oracle or MySQL) are used to COUNT(), M= IN() and MAX() returning almost instantaneously, certainly on indexed colum= ns.  But for reasons that have something to do with transactions,= these operations are unbelievably slow in PostgreSQL.

Here are the alternatives that I've learned.

COUNT() -- Ther= e is no good substitute.  What I do is create a new column, "= ;ROW_NUM" with an auto-incrementing sequence.  Every time I = insert a row, it gets a new value.  Unfortunately, this doesn't w= ork if you ever delete a row.  The alternative is a more complex = pair of triggers, one for insert and one for delete, that maintains the cou= nt in a separate one-row table.  It's a nuisance, but it's a lot = faster than doing a full table scan for every COUNT().

MIN() and MAX() -- These are surprisingly slow, because they seem t= o do a full table scan EVEN ON AN INDEXED COLUMN!  I don't unders= tand why, but happily there is an effective substitute:

  = select mycolumn from mytable order by mycolumn limit 1;  -- same = as MIN()

  select mycolumn from mytable order by mycolumn desc lim= it 1;  -- same as MAX()

For a large table, MIN or MAX can = take 5-10 minutes, where the above "select..." replacements can r= eturn in one millisecond.

You should carefully examine your entire application for COUNT, MIN= , and MAX, and get rid of them EVERYWHERE.  This may be the entir= e source of your problem.  It was in my case.

This is, in = my humble opinion, the only serious flaw in PostgreSQL.  I've bee= n totally happy with it in every other way, and once I understood these sho= rtcomings, my application is runs faster than ever on PostgreSQL.

Craig

------=_Part_26465_15359058.1130103338838--