pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kishore B <kishorebh@gmail.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: [PERFORM] Need help in setting optimal configuration for a huge database.
Date: Mon, 24 Oct 2005 03:25:50 +0530
Message-ID: <42567e060510231455l6a6721cdtc52ad1b4d254aa64@mail.gmail.com> (raw)
In-Reply-To: <435BBAF0.8080500@modgraph-usa.com>
References: <42567e060510221415r2516523l4c3f9b42dee91a09@mail.gmail.com>
	<435BBAF0.8080500@modgraph-usa.com>

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 is
taking so long to execute?
 Thank you,
Kishore.

 On 10/23/05, Craig A. James <cjames@modgraph-usa.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
>

view thread (15+ messages)  latest in thread

Message-ID: <42567e060510231455l6a6721cdtc52ad1b4d254aa64@mail.gmail.com>
Permalink:  ../42567e060510231455l6a6721cdtc52ad1b4d254aa64@mail.gmail.com/
Also on:    postgresql.org/message-id/42567e060510231455l6a6721cdtc52ad1b4d254aa64@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: kishorebh@gmail.com, pgsql-admin@postgresql.org
  Subject: Re: [PERFORM] Need help in setting optimal configuration for a huge database.
  In-Reply-To: <42567e060510231455l6a6721cdtc52ad1b4d254aa64@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox