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 authors_cache
:
Sample rows:
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;
Result:
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.