explain.depesz.com

PostgreSQL's explain analyze made readable

Result: crqG : SELECT COUNT(DISTINCT artifacts.id) FROM "outcomes_production_shard_842"."outcomes" INNER JOIN "outcomes_production_shard_842"."alignments" ON "alignments"."outcome_id" = "outcomes"."id" AND "alignments"."deleted_at" IS NULL INNER JOIN "outcomes_production_shard_842"."alignment_sets" ON "alignment_sets"."id" = "alignments"."alignment_set_id" AND "alignment_sets"."deleted_at" IS NULL INNER JOIN "outcomes_production_shard_842"."artifacts" ON "artifacts"."alignment_set_id" = "alignment_sets"."id" AND "artifacts"."deleted_at" IS NULL WHERE "outcomes"."deleted_at" IS NULL AND "outcomes"."id" IN (69392, 84521, 84603, 41614, 83419, 27138, 41936, 89414, 90197, 90198) AND "artifacts"."artifact_type" = 'quizzes.item' AND "artifacts"."artifact_id" IN (15186, 15187, 15188, 15189, 15190, 15191, 15192, 15193, 15194, 15195, 15196, 15197, 15198, 15199, 15200, 15201, 15202, 15203, 15204, 15205, 15211, 15212, 15206, 15207, 15209, 15208, 15210)

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.021 6,833.664 ↑ 1.0 1 1

Aggregate (cost=81.97..81.98 rows=1 width=8) (actual time=6,833.664..6,833.664 rows=1 loops=1)

2. 0.014 6,833.643 ↓ 8.0 8 1

Nested Loop (cost=53.82..81.96 rows=1 width=8) (actual time=6,094.786..6,833.643 rows=8 loops=1)

  • Join Filter: (alignments.alignment_set_id = alignment_sets.id)
3. 0.185 6,833.573 ↓ 8.0 8 1

Nested Loop (cost=53.54..73.66 rows=1 width=24) (actual time=6,094.754..6,833.573 rows=8 loops=1)

  • Join Filter: (alignments.alignment_set_id = artifacts.alignment_set_id)
  • Rows Removed by Join Filter: 613
4. 11.297 6,799.463 ↓ 23.0 23 1

Nested Loop (cost=53.26..65.31 rows=1 width=8) (actual time=846.107..6,799.463 rows=23 loops=1)

  • Join Filter: (outcomes.id = alignments.outcome_id)
  • Rows Removed by Join Filter: 18,629
5. 1.218 1.218 ↓ 1,879.0 1,879 1

Index Scan using index_alignments_on_alignment_set_id on alignments (cost=0.27..8.28 rows=1 width=16) (actual time=0.024..1.218 rows=1,879 loops=1)

6. 45.096 6,786.948 ↓ 10.0 10 1,879

Bitmap Heap Scan on outcomes (cost=53.00..57.02 rows=1 width=8) (actual time=3.606..3.612 rows=10 loops=1,879)

  • Recheck Cond: ((deleted_at IS NULL) AND (id = ANY ('{69392,84521,84603,41614,83419,27138,41936,89414,90197,90198}'::bigint[])))
  • Heap Blocks: exact=14,940
7. 234.875 6,741.852 ↓ 0.0 0 1,879

BitmapAnd (cost=53.00..53.00 rows=1 width=0) (actual time=3.588..3.588 rows=0 loops=1,879)

8. 6,478.792 6,478.792 ↓ 1,329.5 75,781 1,879

Bitmap Index Scan on index_outcomes_on_parent_id (cost=0.00..8.57 rows=57 width=0) (actual time=3.448..3.448 rows=75,781 loops=1,879)

9. 28.185 28.185 ↑ 1.0 10 1,879

Bitmap Index Scan on outcomes_pkey (cost=0.00..44.18 rows=10 width=0) (actual time=0.015..0.015 rows=10 loops=1,879)

  • Index Cond: (id = ANY ('{69392,84521,84603,41614,83419,27138,41936,89414,90197,90198}'::bigint[]))
10. 33.925 33.925 ↓ 27.0 27 23

Index Scan using index_artifacts_uniqueness_constraint on artifacts (cost=0.28..8.33 rows=1 width=16) (actual time=0.813..1.475 rows=27 loops=23)

  • Index Cond: ((artifact_type)::text = 'quizzes.item'::text)
  • Filter: (artifact_id = ANY ('{15186,15187,15188,15189,15190,15191,15192,15193,15194,15195,15196,15197,15198,15199,15200,15201,15202,15203,15204,15205,15211,15212,15206,15207,15209,15208,15210}'::bigint[]))
  • Rows Removed by Filter: 2,307
11. 0.056 0.056 ↑ 1.0 1 8

Index Scan using alignment_sets_pkey on alignment_sets (cost=0.28..8.29 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=8)

  • Index Cond: (id = artifacts.alignment_set_id)
  • Filter: (deleted_at IS NULL)
Planning time : 11.272 ms
Execution time : 6,833.747 ms