Public
Authored by Adam Hegyi

Weird index usage

This query uses the index_issues_on_project_id_and_created_at_and_id_and_state index.

EXPLAIN ANALYZE
SELECT count(*)
FROM issues
INNER JOIN label_links ON label_links.target_type='Issue'
AND label_links.target_id=issues.id
WHERE issues.created_at > '2019-08-01'
  AND issues.project_id = 278964
  AND label_links.label_id IN (2731248,
                               10230929,
                               4116705,
                               2492649,
                               2278648,
                               2779806)

This query uses the issues_pkey index, which makes the execution much slower, the only difference is one additional join (labels):

EXPLAIN ANALYZE
SELECT count(*)
FROM issues
INNER JOIN label_links ON label_links.target_type='Issue'
AND label_links.target_id=issues.id
INNER JOIN labels ON labels.id=label_links.label_id
WHERE issues.created_at > '2019-08-01'
  AND issues.project_id = 278964
  AND labels.id IN (2731248,
                    10230929,
                    4116705,
                    2492649,
                    2278648,
                    2779806)
file 3 Bytes
Markdown is supported
0% or
You are about to add 0 people to the discussion. Proceed with caution.
Finish editing this message first!
Please register or to comment