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

Re: Need help in setting optimal configuration for a huge database.

From: Kishore B <kishorebh(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Need help in setting optimal configuration for a huge database.
Date: 2005-10-23 21:35:38
Message-ID: 42567e060510231435g113273edidc97207a71abf5f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-performance
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(at)modgraph-usa(dot)com> 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, these
> 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 row.
> The alternative is a more complex pair of triggers, one for insert and one
> 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 your
> 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 these
> shortcomings, my application is runs faster than ever on PostgreSQL.
>
> Craig
>

In response to

pgsql-performance by date

Next:From: Kishore BDate: 2005-10-23 21:55:50
Subject: Re: [PERFORM] Need help in setting optimal configuration for a huge database.
Previous:From: Josh BerkusDate: 2005-10-23 21:02:35
Subject: Re: Using LIMIT 1 in plpgsql PERFORM statements

pgsql-admin by date

Next:From: Kishore BDate: 2005-10-23 21:55:50
Subject: Re: [PERFORM] Need help in setting optimal configuration for a huge database.
Previous:From: Tomasz RybakDate: 2005-10-23 17:17:56
Subject: Re: Need help in setting optimal configuration for a huge

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