From: "Carlo Stonebanks" <stonec.register@sympatico.ca>
Subject: Re: Performace Optimization for Dummies
Date: Thu, 28 Sep 2006 15:47:15 -0400
Organization: Hub.Org Networking Services
Lines: 42
Message-ID: <efh8s6$1dlc$1@news.hub.org>
References: <efgu4v$276m$1@news.hub.org>
	<00d001c6e32b$aaaa8a80$8300a8c0@tridecap.com>
To: pgsql-performance@postgresql.org

> 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?

There are cross-referencing and deduplication processes. Does this person 
have an office at this exact address? In a similarily named building in the 
same zip code? City? What is the similarity of the building or enterprise 
names? Is there a person with a similar name with the same type of 
professional license nearby? We basically look for the statistical 
likelyhood that they already exist to decide whether to update their data, 
or insert a new data element.

These are all extremely soft queries and require left outer joins with all 
of the related tables that would contain this data (the left outer join 
tells us whether the related element satisfied the search condition). As I 
mentioned, as the data comes in, we examine what we have to work with and 
modify the tables and columns we can check - which is what I meant by " the 
SQL queries are mutable, every imported row can change the structure of a 
SQL query as the program adds and subtracts search conditions to the SQL 
command text before execution."

> 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.

I'm pretty sure I know who the culprit is, and - like I said, it comes from 
a section of code that creates a mutable statement. However, everyone is 
being so helpful and I should post this data as soon as I can.

> 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)

I am using regular expressions and fuzzy logic, but mostly on the client 
side (I have a Tcl implementation of levenshtein, for example). I don't 
think you can use indexes on functions such as levenshtein, because it 
requires a parameter only available at execution time. The link you sent me 
was very interesting - I will definitely reconsider my query logic if I can 
optimise regular expression searches on the server. Thanks!

Carlo