Re: postgre performance question

From: Doug McNaught <doug(at)wireboard(dot)com>
To: Ioannis Kappas <Ioannis(dot)Kappas(at)dante(dot)org(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: postgre performance question
Date: 2002-03-04 15:34:12
Message-ID: m3henwmjnv.fsf@varsoon.denali.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Ioannis Kappas <Ioannis(dot)Kappas(at)dante(dot)org(dot)uk> writes:

> ... it really does clean the table at midnight and then immediately
> vacuums the table after it.
> What it really does is to populate the table with two hundred thousand
> of entries each day and
> later on the table will be populated with million of entries each day.
> Again at midnight, all the entries from the table are removed and the
> table is vacuumed (I want to make this clear).

Thanks for the clarification. Are you doing a lot of updates during
the day, or just inserts?

> Do you think this is the expected behaviour I am getting? Can I do
> something to improve the
> perfrormance? Should I try to find another database that can handle
> such `big?' amount of entries?
> Can I change something on the configuration of the database that will
> speed up the queries?

Well, if you're selecting every record from a table with millions of
records, any database is going to be slow. There, the bottleneck is
disk i/o and how fast the server can send data to the client.

For more selective queries, make sure you:

1) VACUUM ANALYZE (or just ANALYZE in 7.2) after the table is populated.
2) Put indexes on the appropriate columns (depends on what queries you
make).

Without seeing your schema and the queries you're running, it's hard
to give you any more advice.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Doug McNaught 2002-03-04 15:37:24 Re: backend closed
Previous Message Tom Lane 2002-03-04 15:28:06 Re: descending index