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.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend