Using Full Text Search to create TurnItIn light

I've been having my students post journal entries to a Drupal site, and we just noticed that some of them have been copying from each other (surprise, surprise). If this was done with reports or other submissions, we'd just submit the deliverables to TurnItIn.com and go from there.

In this case I'd rather run the similarity checking on our local server, and my guess is that Full Text Search is the way to go. I've found a couple of online posts about such things, but they're not quite as clear as I was hoping. I'm hoping that the list can help me out (and keep this from becoming an O(n^4) challenge).

What I have is essentially a table containing an ID and some TEXT.

My first attempt used the similarity() function from pg_trgm as follows:

select
aid
,bid
,similarity(source,dest) sim
from
(
select
a.id aid
,a.body source
,b.id bid
,b.body dest
from
posts a
cross join
posts b
where
and a.id != b.id
) as inside
order by sim desc;

I haven't been thinking in SQL terms for a while, so I didn't come up with a way to eliminate half of my checks by exploiting symmetry (a simple way to accomplish this would be helpful). Unfortunately this solution will take way too long to execute.

I'm hoping that the full text features can be shoehorned into this problem, but from what little I've seen it will be tricky because comparing a tsvector to another tsvector isn't supported. Plus there's weird (to the novice) indexing that will (I hope) help to keep the complexity down.

Thoughts from the list are most welcome, and I'll write up a HOWTO once I get this working.

Thanks!

Jason