Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 8AAA41337BBB
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 11 Mar 2011 12:47:09 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 62814-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 11 Mar 2011 16:46:59 +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 1069D1337C0C
	for <pgsql-performance@postgresql.org>;
	Fri, 11 Mar 2011 12:46:52 -0400 (AST)
Received: from list by lo.gmane.org with local (Exim 4.69)
	(envelope-from <gcdpp-pgsql-performance@m.gmane.org>)
	id 1Py5UH-0003g5-BU
	for pgsql-performance@postgresql.org; Fri, 11 Mar 2011 17:46:49 +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 17:46:49 +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 17:46:49 +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: Fri, 11 Mar 2011 16:46:28 +0000 (UTC)
Lines: 30
Message-ID: <loom.20110311T173103-318@post.gmane.org>
References: <D1D5E528-DAEB-4E4F-A3E6-EA2A95340D1A@vizbang.com>
	<6C998CEA-5A42-4DA9-B5E0-33C17C1BF69E@blighty.com>
	<loom.20110311T003919-939@post.gmane.org>
	<2B78E04A-8511-4E3A-9BDF-09905237642A@vizbang.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/119
X-Sequence-Number: 42798

Dan Ancona <da <at> vizbang.com> writes:

>  his is a choice between  
> developing some in-house capacity for this and sending people to  
> various vendors so we'll probably lean on the vendors for now, at  
> least while we work on it. 

I would try to do the record matching in house and see how far you get, even if
you are talking to vendors concurrently.  You might get lucky, and you will
learn a lot about your data and how much to expect and pay for vendor solutions.

I would: Try building multi column indices on both tables for what you think are
the same rows, and match deterministically (if you have a key like social
security, then do this again on full names).  Examine your data to see what
hits, what misses, what hits multiple.  If you know there is a "good" and an
"iffy" table, you can use a left outer, otherwise you need a full outer.   Then
put all your leftovers from each into new tables, and try again with something
fuzzy.

If you build the indices and use "=" and it is still slow, ask again here --
that shouldn't happen.

> And you're right fork, Record Linkage is in fact an entire academic  
> discipline!

Indeed.  Look for "blocking" and "editing" with your data first, I think.

I find this problem pretty interesting, so I would love to hear your results.  I
am right now matching building permits to assessor parcels....  I wish I was
using PG ...