explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XG8O

Settings
# exclusive inclusive rows x rows loops node
1. 0.370 143.974 ↓ 158.7 476 1

Unique (cost=1,473.83..1,473.95 rows=3 width=143) (actual time=143.560..143.974 rows=476 loops=1)

2. 0.691 143.604 ↓ 174.7 524 1

Sort (cost=1,473.83..1,473.84 rows=3 width=143) (actual time=143.559..143.604 rows=524 loops=1)

  • Sort Key: defect_families.id, defect_families.name, defect_families.description, defect_families.display, defect_families.created_at, defect_families.updated_at, defect_families.variety_id, defect_families.total, defect_families.accept_issues_total, defect_families.rating_id, defect_families.document, defect_families.company_id, defect_families.archived, defect_families.specification_import_id
  • Sort Method: quicksort Memory: 134kB
3. 59.088 142.913 ↓ 174.7 524 1

Hash Left Join (cost=320.10..1,473.81 rows=3 width=143) (actual time=41.738..142.913 rows=524 loops=1)

  • Hash Cond: (programs_users.user_id = users.id)
  • Filter: (((users.id = 245) AND programs.active AND (programs.start_date <= '2019-06-07'::date) AND (programs.end_date >= '2019-06-07'::date)) OR (defect_families.id IS NULL))
  • Rows Removed by Filter: 183498
4. 64.989 80.795 ↓ 6.3 184,022 1

Hash Left Join (cost=122.44..1,199.65 rows=29,071 width=156) (actual time=2.180..80.795 rows=184,022 loops=1)

  • Hash Cond: (product_groups.program_id = programs.id)
5. 2.829 14.746 ↓ 1.3 5,461 1

Hash Left Join (cost=84.14..901.19 rows=4,065 width=147) (actual time=1.111..14.746 rows=5,461 loops=1)

  • Hash Cond: (defect_family_ratings.product_group_id = product_groups.id)
6. 3.740 10.840 ↓ 1.3 5,461 1

Merge Left Join (cost=0.57..806.93 rows=4,065 width=147) (actual time=0.026..10.840 rows=5,461 loops=1)

  • Merge Cond: (defect_families.id = defect_family_ratings.defect_family_id)
7. 3.690 3.690 ↑ 1.0 3,013 1

Index Scan using defect_families_pkey on defect_families (cost=0.28..274.92 rows=3,013 width=143) (actual time=0.013..3.690 rows=3,013 loops=1)

8. 3.410 3.410 ↑ 4.6 4,066 1

Index Scan using index_defect_family_ratings_on_defect_family_id on defect_family_ratings (cost=0.29..2,199.97 rows=18,889 width=8) (actual time=0.008..3.410 rows=4,066 loops=1)

9. 0.509 1.077 ↑ 1.0 1,492 1

Hash (cost=64.92..64.92 rows=1,492 width=8) (actual time=1.077..1.077 rows=1,492 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 75kB
10. 0.568 0.568 ↑ 1.0 1,492 1

Seq Scan on product_groups (cost=0.00..64.92 rows=1,492 width=8) (actual time=0.007..0.568 rows=1,492 loops=1)

11. 0.322 1.060 ↓ 1.2 875 1

Hash (cost=29.54..29.54 rows=701 width=17) (actual time=1.060..1.060 rows=875 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
12. 0.511 0.738 ↓ 1.2 875 1

Hash Right Join (cost=4.54..29.54 rows=701 width=17) (actual time=0.103..0.738 rows=875 loops=1)

  • Hash Cond: (programs_users.program_id = programs.id)
13. 0.138 0.138 ↑ 1.0 944 1

Seq Scan on programs_users (cost=0.00..14.44 rows=944 width=8) (actual time=0.009..0.138 rows=944 loops=1)

14. 0.034 0.089 ↑ 1.0 98 1

Hash (cost=3.32..3.32 rows=98 width=13) (actual time=0.089..0.089 rows=98 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
15. 0.055 0.055 ↑ 1.0 98 1

Seq Scan on programs (cost=0.00..3.32 rows=98 width=13) (actual time=0.012..0.055 rows=98 loops=1)

  • Filter: active
  • Rows Removed by Filter: 34
16. 0.748 3.030 ↑ 1.0 2,848 1

Hash (cost=162.06..162.06 rows=2,848 width=4) (actual time=3.029..3.030 rows=2,848 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 133kB
17. 2.282 2.282 ↑ 1.0 2,848 1

Seq Scan on users (cost=0.00..162.06 rows=2,848 width=4) (actual time=0.008..2.282 rows=2,848 loops=1)

  • Filter: (NOT archived)
  • Rows Removed by Filter: 58