3 ms·
OP here. The lack of an index on the junction table definitely did have a major effect. By just doing the following: CREATE INDEX ON movie_person (movie_i
by zacharyvoase 14y ago
OP here.
The lack of an index on the junction table definitely did have a major effect. By just doing the following:
CREATE INDEX ON movie_person (movie_id);
CREATE INDEX ON movie_person (person_id);
The junction query speeds up to around 2ms—it's comparable to or faster than the bloom filter query. But the trade-off is revealed when you see the total size of the movie_person table (including indexes):
SELECT pg_size_pretty(pg_total_relation_size('movie_person'));
=> 45MB
Whereas, by my calculations, the total size added by the bloom filters and hashes on movie and person is just 2094kB in total.
I plan on adding an erratum to my article explaining my error, the time/memory trade-off, and ideas for further improvement or exploration, potentially including bloom-based GiST indexes and the opportunities for parallelization.
- fdr 14y agoAlso look at GIN. Although the documentation claims it is typically larger than GIST, in certain cases (a small lexeme set, which is not true in the general case full-text) it can seem to be smaller and faster. Mostly this can be true over constrained symbolic domains (which is a lot of computing also). We've recently replaced a GIST index with a GIN one and got a lot more than the 'rule of thumb' speedup. More like an order of magnitude. Or two. So much, that Tom Lane suggested we might have stumbled onto a bug in GIST, so we'll see.