explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nUgv

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

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

2. 0.696 142.609 ↓ 174.7 524 1

Sort (cost=1,473.83..1,473.84 rows=3 width=143) (actual time=142.564..142.609 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.595 141.913 ↓ 174.7 524 1

Hash Left Join (cost=320.10..1,473.81 rows=3 width=143) (actual time=41.294..141.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. 65.211 80.593 ↓ 6.3 184,022 1

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

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

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

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

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

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

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

9. 0.399 0.851 ↑ 1.0 1,492 1

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

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

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

11. 0.298 0.984 ↓ 1.2 875 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
12. 0.476 0.686 ↓ 1.2 875 1

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

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

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

14. 0.033 0.083 ↑ 1.0 98 1

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

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

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

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

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

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

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

  • Filter: (NOT archived)
  • Rows Removed by Filter: 58
Planning time : 2.096 ms
Execution time : 143.118 ms