My experience has been that the performance advantage for numeric keys is
primarily an Oracle thing. However, Oracle is popular enough for people to
assume that it applies to databases in general.
Julien Cigar jci...@ulb.ac.be wrote:
The biggest difference in performance between text and
Yes. And this has little to do with hints. It has to do with years
of development lead with THOUSANDS of engineers who can work on the
most esoteric corner cases in their spare time. Find the pg project a
couple hundred software engineers and maybe we'll catch Oracle a
little quicker.
On PostgreSQL, the difference in no hints and hints for that one query
with skewed data is that the query finishes a little faster. On some
others, which shall remain nameless, it is the difference between
finishing in seconds or days, or maybe never. Hints can be useful, but
I can also
Instead of something like 'shake' (which more or less works, even
though it doesn't use fallocate and friends) I frequently use either
CLUSTER (which is what Greg Smith is suggesting) or a series of ALTER
TABLE ... ALTER COLUMN... which rewrites the table. With PG 9 perhaps
VACUUM FULL is more
Putting the WAL on a second controller does help, if you're write-heavy.
I tried separating indexes and data once on one server and didn't
really notice that it helped much. Managing the space was problematic.
I would suggest putting those together on a single RAID-10 of all the
300GB
