In a Postgres 9.4.11 database, I have a large table of books, which has a one-to-many relationship with authors. Theoretically to improve performance, I've cached the author names in the ebook record for quick searching. Properties of the column
I've created a GIN index on this column:
CREATE INDEX "index_ebooks_on_authors_cache" ON "ebooks" USING gin ("authors_cache")
It's not using the index when searching
authors_cache and takes a unacceptable amount of time:
EXPLAIN ANALYZE SELECT * FROM "ebooks" WHERE ('Charles Bukowski' = ANY (authors_cache)) LIMIT 60 OFFSET 0;
Limit (cost=0.00..172334.67 rows=30 width=124) (actual time=71.962..10067.070 rows=59 loops=1) -> Seq Scan on ebooks (cost=0.00..172334.67 rows=30 width=124) (actual time=71.960..10067.015 rows=59 loops=1) Filter: ('Charles Bukowski'::text = ANY ((authors_cache)::text)) Rows Removed by Filter: 894504 Planning time: 0.188 ms Execution time: 10067.112 ms
10 seconds is not an acceptable amount of time. I'm open to design changes, as I'm not "married" to this column, yet.