Re: Query optimizing - paradox behave - Mailing list pgsql-sql

From Josh Berkus
Subject Re: Query optimizing - paradox behave
Date
Msg-id web-88042@davinci.ethosmedia.com
Whole thread Raw
In response to Query optimizing - paradox behave  ("David M. Richter" <D.Richter@DKFZ-heidelberg.de>)
List 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@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Attachment

pgsql-sql by date:

Previous
From: "Josh Berkus"
Date:
Subject: Re: pl/pgsql - code review + question
Next
From: Dado Feigenblatt
Date:
Subject: nextval on insert by arbitrary sequence