From: "Dave Dutcher" <dave@tridecap.com>
To: "'Carlo Stonebanks'" <stonec.register@sympatico.ca>,
	<pgsql-performance@postgresql.org>
Subject: Re: Performace Optimization for Dummies
Date: Thu, 28 Sep 2006 13:26:49 -0500
Message-ID: <00d001c6e32b$aaaa8a80$8300a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <efgu4v$276m$1@news.hub.org>
Importance: Normal

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Carlo
Stonebanks
> Subject: [PERFORM] Performace Optimization for Dummies
>=20
> At this early stage in the project, we are initializing our portal's=20
> database with millions of rows of imported data in over 50 different=20
> flattened tables; each table's structure is unique to the=20
> data provider.=20
> This requires a pretty complex import program, because the=20
> data must be=20
> matched semantically, not literally. Even with all of the expression=20
> matching and fuzzy logic in the code,our performance=20
> statistics show that=20
> the program spends over 75% of its time in SQL queries=20
> looking for matching=20
> and/or duplicate data.
>=20
> The import is slow - and degrades as the tables grow.=20

So your program first transforms the data and then inserts it?  And it =
is
the transforming process which is running select statements that is =
slow?
If that is the case you could use duration logging to find the slow =
select
statement, and then you could post an EXPLAIN ANALYZE of the select. =20

One question off the top of my head is are you using regular expressions =
for
your fuzzy logic if so do your indexes have the right operator classes?
(see http://www.postgresql.org/docs/8.1/static/indexes-opclass.html)

Dave