explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6qoZ : Optimization for: 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); plan #crqG

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.025 54.455 ↑ 1.0 1 1

Aggregate (cost=81.84..81.85 rows=1 width=8) (actual time=54.454..54.455 rows=1 loops=1)

2. 0.023 54.430 ↓ 8.0 8 1

Nested Loop (cost=1.24..81.83 rows=1 width=8) (actual time=38.628..54.430 rows=8 loops=1)

  • Join Filter: (alignments.alignment_set_id = alignment_sets.id)
3. 0.271 54.375 ↓ 8.0 8 1

Nested Loop (cost=0.96..73.53 rows=1 width=24) (actual time=38.613..54.375 rows=8 loops=1)

  • Join Filter: (alignments.alignment_set_id = artifacts.alignment_set_id)
  • Rows Removed by Join Filter: 613
4. 3.482 8.426 ↓ 23.0 23 1

Nested Loop (cost=0.68..65.18 rows=1 width=8) (actual time=2.309..8.426 rows=23 loops=1)

5. 1.186 1.186 ↓ 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.017..1.186 rows=1,879 loops=1)

6. 3.758 3.758 ↓ 0.0 0 1,879

Index Only Scan using outcomes_pkey on outcomes (cost=0.41..48.21 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1,879)

  • Index Cond: ((id = alignments.outcome_id) AND (id = ANY ('{69392,84521,84603,41614,83419,27138,41936,89414,90197,90198}'::bigint[])))
  • Heap Fetches: 0
7. 45.678 45.678 ↓ 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.853..1.986 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
8. 0.032 0.032 ↑ 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.004..0.004 rows=1 loops=8)

  • Index Cond: (id = artifacts.alignment_set_id)
  • Filter: (deleted_at IS NULL)
Planning time : 0.913 ms
Execution time : 54.536 ms