Re: Performace Optimization for Dummies

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

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.

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