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.