News for hackers. Stuff that matters.
Re:Probabilistic Many-to-Many Relationships (with Bloom Filt... by wiredfool on Friday August 31, 2012 @03:00PM (#4461159)
Here's his problem:

  ->  Seq Scan on movie_person  (cost=0.00..10117.01 rows=97 width=8) 
       (actual time=2.540..59.933 rows=9 loops=1)
        Filter: (person_id = 160)
If he had an index on movie_person, that would be a ~ 1ms index lookup, not a table scan. You can't do that sort of database layout without putting indexes on the M2M mapping table.

(edit, oh, yeah, it's right there in the parent post....)

Re:Probabilistic Many-to-Many Relationships (with Bloom Filt... by nierman on Friday August 31, 2012 @03:08PM (#4461201)
and you probably want to include these indices in any case to enforce that a given (user, movie) pair in the join table is unique (unless you want to track if a rating was changed and when, etc.)

further, you might decide to cluster on the index or partition.

Re:Probabilistic Many-to-Many Relationships (with Bloom Filt... by wiredfool on Friday August 31, 2012 @04:13PM (#4461485)
Yep. You'd probably wind up wanting two indexes, a unique one on user,movie and then possibly one on movie only, to speed lookups the other way.