explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J7Pr9

Settings
# exclusive inclusive rows x rows loops node
1. 0.373 143.360 ↓ 158.7 476 1

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

2. 0.661 142.987 ↓ 174.7 524 1

Sort (cost=1,473.83..1,473.84 rows=3 width=143) (actual time=142.942..142.987 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. 58.520 142.326 ↓ 174.7 524 1

Hash Left Join (cost=320.10..1,473.81 rows=3 width=143) (actual time=42.543..142.326 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.956 80.585 ↓ 6.3 184,022 1

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

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

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

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

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

  • Merge Cond: (defect_families.id = defect_family_ratings.defect_family_id)
7. 3.597 3.597 ↑ 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.017..3.597 rows=3,013 loops=1)

8. 3.557 3.557 ↑ 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.009..3.557 rows=4,066 loops=1)

9. 0.442 0.949 ↑ 1.0 1,492 1

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

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

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

11. 0.358 1.200 ↓ 1.2 875 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
12. 0.598 0.842 ↓ 1.2 875 1

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

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

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

14. 0.037 0.093 ↑ 1.0 98 1

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

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

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

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

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

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

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

  • Filter: (NOT archived)
  • Rows Removed by Filter: 58
Planning time : 2.240 ms
Execution time : 143.545 ms