3

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:

screenshot of authors_cache

Sample rows:

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.

1
  • Note that we very much prefer data as text, never screenshots. For future questions. Oct 8, 2017 at 19:56

1 Answer 1

7

Use array operators which are supported by your GIN index. The = ANY () construct is not.

SELECT *
FROM   ebooks
WHERE  authors_cache @> '{Charles Bukowski}'  -- array literal
LIMIT  60;

Detailed explanation:

Not sure your de-normalization will buy you much. A standard 1:n implementation with simple btree indexes should perform nicely, too.

If you are going with this approach, I would consider storing an array of integer (int[]) with author_id's. Substantially smaller array column and GIN index. Potential problems with referential integrity are about the same. (Array elements cannot currently have FK constraints. Attempts to implement this in Postgres were unsuccessful so far.)

Oh, and consider upgrading. Performance with GIN indexes has been improved substantially since Postgres 9.4.

1
  • Your solution worked like a CHARM. Thank you so much. In regards to your comment about storing author_id's, yes that definitely makes the most logical sense. However, when I search for these books, I need to render the results. If I store the actual name, then I wouldn't have to do subsequent queries (or joins) for the actual author names. I'm also doing this for "publishers, and the book genre(s)". I'm guessing that I will lose time on the search, but overall will reduce response times.
    – Andrew Wei
    Oct 8, 2017 at 3:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.