Extremely frequent query

[erikgrinaker]

Variations of this query are run over 500.000 times per day, making up 92% of all database queries combined from all our apps. The average query duration is 0.134 seconds, which means our Grove workers are spending about 1250 minutes every day just waiting for this to return. If we could somehow cache it, or better yet, cache the data structured returned by the calling app, it should translate to huge savings.

SELECT
    "posts".*
FROM
    "posts"
WHERE
    "posts"."external_id" IN ('response_topic_bygdeposten_conversation_5340116' )
    AND "posts"."external_id" = 'response_topic_bygdeposten_conversation_5340116'
    AND (NOT deleted )
    AND (published OR published IS NULL )
LIMIT 1;

[atombender]

You can improve that type of query slightly creating a partial index (reduces cost by 25%).

create index index_posts_on_realm_and_external_id_where_etc on posts (realm, external_id)
  where not deleted and (published or published is null);