X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4AA93DAAEB for ; Sun, 23 Oct 2005 18:55:49 -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 74304-07 for ; Sun, 23 Oct 2005 21:55:46 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.204]) by svr1.postgresql.org (Postfix) with ESMTP id 6985ADAAAC for ; Sun, 23 Oct 2005 18:55:47 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id 8so505340nzo for ; Sun, 23 Oct 2005 14:55:50 -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=LuSGTdARHK1RwkJa5XbsBR2NeJ+Vw/Dl/guJkmC4F+H9VO3wNpn3RhgFdz5seF3wxUJZDMK7VEXblJUDl4LNKvCuSHmfiW4fS4V/I+32XcziQxgc49oYaPqsAL8Oo92vzVHY1fN+eQvo1fj7kouOHoAxDcsgCwoIJ881Mv//Wkg= Received: by 10.36.220.69 with SMTP id s69mr5187815nzg; Sun, 23 Oct 2005 14:55:50 -0700 (PDT) Received: by 10.36.61.1 with HTTP; Sun, 23 Oct 2005 14:55:50 -0700 (PDT) Message-ID: <42567e060510231455l6a6721cdtc52ad1b4d254aa64@mail.gmail.com> Date: Mon, 24 Oct 2005 03:25:50 +0530 From: Kishore B To: "pgsql-admin@postgresql.org" Subject: Re: [PERFORM] 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_26561_3347224.1130104550168" 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.709 required=5 tests=[AWL=-0.290, HTML_20_30=0.504, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024, TO_ADDRESS_EQ_REAL=0.47] X-Spam-Level: X-Archive-Number: 200510/251 X-Sequence-Number: 19323 ------=_Part_26561_3347224.1130104550168 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Craig, Does the no of tables and the size of each table affect the performance of a join operation? When we are trying to join the two big tables that I described above, pg i= s taking so long to execute? 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_26561_3347224.1130104550168 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi Craig,
 
Does the no of tables and the size of each table affect the performanc= e of a join operation?
 
When we are trying to join the two big tables that I described above, = pg is taking so long to execute?
 
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_26561_3347224.1130104550168--