Dan Harris <[EMAIL PROTECTED]> writes:

> Well, once every day, but there aren't a ton of inserts or updates  going on a
> daily basis.  Maybe 1,000 total inserts?

It's actually deletes and updates that matter. not inserts.

> I have a feeling I'm going to need to do a cluster soon.  I have done  several
> mass deletes and reloads on it.

CLUSTER effectively does a VACUUM FULL but takes a different approach and
writes out a whole new table, which if there's lots of free space is faster
than moving records around to compact the table.

> I tried that, and indeed it was using an index, although after  reading 
> Simon's
> post, I realize that was kind of dumb to have an  index on a bool. I have 
> since
> removed it.

If there are very few records (like well under 10%) with that column equal to
false (or very few equal to true) then it's not necessarily useless. But
probably more useful is a partial index on some other column.

Something like 

CREATE INDEX ON pk WHERE flag = false;

> No foreign keys or triggers.

Note that I'm talking about foreign keys in *other* tables that refer to
columns in this table. Every update on this table would have to scan those
other tables looking for records referencing the updated rows.

> Ok, so I remounted this drive as ext2 shortly before sending my first  email
> today.  It wasn't enough time for me to notice the ABSOLUTELY  HUGE difference
> in performance change.  Ext3 must really be crappy  for postgres, or at least
> is on this box.  Now that it's ext2, this  thing is flying like never before.
> My CPU utilization has  skyrocketed, telling me that the disk IO was
> constraining it immensely.
> I always knew that it might be a little faster, but the box feels  like it can
> "breathe" again and things that used to be IO intensive  and run for an hour 
> or
> more are now running in < 5 minutes.  I'm a  little worried about not having a
> journalized file system, but that  performance difference will keep me from
> switching back ( at least to  ext3! ).  Maybe someday I will try XFS.


I wonder if ext3 might be issuing IDE cache flushes on every fsync (to sync
the journal) whereas ext2 might not be issuing any cache flushes at all.

If the IDE cache is never being flushed then you'll see much better
performance but run the risk of data loss in a power failure or hardware
failure. (But not in the case of an OS crash, or at least no more than

You could also try using the "-O journal_dev" option to put the ext3 journal
on a separate device.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?


Reply via email to