Re: Query optimizing - paradox behave

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: "David M(dot) Richter" <D(dot)Richter(at)DKFZ-heidelberg(dot)de>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Query optimizing - paradox behave
Date: 2001-07-19 15:31:08
Message-ID: web-88042@davinci.ethosmedia.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-sql

David,

You will no doubt hear later from the tuning experts on the list.
However, let me save everybody some time by verifying some basics:

1. When you restructured the database, you ran VACUUM ANALYZE on the new
database (pacs)?

2. You said that you "eliminated the indexes" because they weren't
helping performance. Is this right? It seems a little hard to figure
from here.

3. General Advice: If you're concerned about query performance, get rid
of those VARCHAR(80) primary keys and replace them with INT4 or INT8!
The math is easy to do:
If you're processing INT8 keys for 1,000,000 table rows that's
8,000,000 (roughly 8mb) data on disk and data being processed.
If you're processing VARCHAR(80) keys for 1,000,000 table rows, thats
82,000,000 bytes (82 mb) on disk and in ram to be processed.
In theory, you could get a 10-fold increase in JOIN performance by
switching to INT8 keys. In practice, its probably more like double or
triple but that ain't bad, either.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco

Attachment Content-Type Size
unknown_filename text/plain 2 bytes
unknown_filename text/plain 2 bytes
unknown_filename text/plain 2 bytes

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Nils Zonneveld 2001-07-19 22:51:12 Re: Get the tables names?
Previous Message danno 2001-07-19 15:28:26 querying values defined in a check constraint