From: | "Scott Marlowe" <smarlowe(at)qwest(dot)net> |
---|---|
To: | "Josh Close" <narshe(at)gmail(dot)com> |
Cc: | "POSTGRES" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: how much ram do i give postgres? |
Date: | 2004-10-20 15:52:25 |
Message-ID: | 1098287545.21035.54.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 2004-10-20 at 07:25, Josh Close wrote:
> It's slow due to several things happening all at once. There are a lot
> of inserts and updates happening. There is periodically a bulk insert
> of 500k - 1 mill rows happening. I'm doing a vacuum anaylyze every
> hour due to the amount of transactions happening, and a vacuum full
> every night. All this has caused selects to be very slow. At times, a
> "select count(1)" from a table will take several mins. I don't think
> selects would have to wait on locks by inserts/updates would it?
1: Is the bulk insert being done inside of a single transaction, or as
individual inserts?
2: Are your fsm settings high enough for an hourly vacuum to be
effective?
3: How selective is the where clause for your select (1) query? If
there is no where clause or the where clause isn't very selective, then
there will be a sequential scan every time. Since PostgreSQL has to hit
the table after using an index anyway, if it's going to retrieve a fair
percent of a table, it just goes right to a seq scan, which for
postgresql, is the right thing to do.
Post "explain analyze" of your slowest queries to the performance list
if you can.
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2004-10-20 15:55:27 | Re: index not used? |
Previous Message | Aaron Mulder | 2004-10-20 15:47:12 | Free PostgreSQL Training, Philadelphia, Oct 30 |