Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 3D7391337BBB
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 10 Mar 2011 19:49:19 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 57364-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 10 Mar 2011 23:49:12 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from lo.gmane.org (lo.gmane.org [80.91.229.12])
	by mail.postgresql.org (Postfix) with ESMTP id 669311337BBA
	for <pgsql-performance@postgresql.org>;
	Thu, 10 Mar 2011 19:49:11 -0400 (AST)
Received: from list by lo.gmane.org with local (Exim 4.69)
	(envelope-from <gcdpp-pgsql-performance@m.gmane.org>)
	id 1PxpbO-0004Wn-3y
	for pgsql-performance@postgresql.org; Fri, 11 Mar 2011 00:49:06 +0100
Received: from 198.239.99.92 ([198.239.99.92])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-performance@postgresql.org>; Fri, 11 Mar 2011 00:49:06 +0100
Received: from forkandwait by 198.239.99.92 with local (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-performance@postgresql.org>; Fri, 11 Mar 2011 00:49:06 +0100
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-performance@postgresql.org
From: fork <forkandwait@gmail.com>
Subject: Re: big joins not converging
Date: Thu, 10 Mar 2011 23:48:53 +0000 (UTC)
Lines: 36
Message-ID: <loom.20110311T003919-939@post.gmane.org>
References: <D1D5E528-DAEB-4E4F-A3E6-EA2A95340D1A@vizbang.com>
	<6C998CEA-5A42-4DA9-B5E0-33C17C1BF69E@blighty.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: sea.gmane.org
User-Agent: Loom/3.14 (http://gmane.org/)
X-Loom-IP: 198.239.99.92 (Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.9.2.15) Gecko/20110303 Firefox/3.6.15)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.735 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_NUMERIC_HELO=1.164,
	T_RP_MATCHES_RCVD=-0.01, T_TO_NO_BRKTS_FREEMAIL=0.01
X-Spam-Level: 
X-Archive-Number: 201103/115
X-Sequence-Number: 42794

Steve Atkins <steve <at> blighty.com> writes:

> 
> 
> On Mar 10, 2011, at 1:25 PM, Dan Ancona wrote:
> 
> > Hi postgressers -
> > 
> > As part of my work with voter file data, I pretty regularly have to join one
large-ish (over 500k rows) table
> to another. Sometimes this is via a text field (countyname) + integer (voter
id). I've noticed sometimes
> this converges and sometimes it doesn't, seemingly regardless of how I index
things. 

By "converge" you mean "finish running" -- "converge" has a lot of other
overtones for us amateur math types.

Note that I think you are doing "record linkage" which is a stepchild academic
of its own these days.  It might bear some research.  THere is also a CDC
matching program for text files freely downloadalbe to windows (ack), if you
hunt for it.

For now, my first thought is that you should try a few different matches, maybe
via PL/PGSQL functions, cascading the non-hits to the next step in the process
while shrinking your tables. upcase and delete all spaces, etc. First use
equality on all columns, which should be able to use indices, and separate those
records.  Then try equality on a few columns.  Then try some super fuzzy regexes
on a few columns.  Etc.  

You will also have to give some thought to scoring a match, with perfection a
one, but, say, name and birthday the same with all else different a .75, etc.

Also, soundex(), levenshtein, and other fuzzy string tools are your friend.  I
want to write a version of SAS's COMPGED for Postgres, but I haven't got round
to it yet.