From: fork <forkandwait@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: big joins not converging
Date: Fri, 11 Mar 2011 16:46:28 +0000 (UTC)
Message-ID: <loom.20110311T173103-318@post.gmane.org> (raw)
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>
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 ...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: forkandwait@gmail.com
Subject: Re: big joins not converging
In-Reply-To: <loom.20110311T173103-318@post.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox