explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cxRm

Settings
# exclusive inclusive rows x rows loops node
1. 0.372 139.321 ↓ 158.7 476 1

Unique (cost=1,475.33..1,475.44 rows=3 width=143) (actual time=138.905..139.321 rows=476 loops=1)

2. 0.715 138.949 ↓ 174.7 524 1

Sort (cost=1,475.33..1,475.34 rows=3 width=143) (actual time=138.904..138.949 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. 57.386 138.234 ↓ 174.7 524 1

Hash Left Join (cost=321.59..1,475.31 rows=3 width=143) (actual time=40.861..138.234 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. 63.414 77.870 ↓ 6.3 184,022 1

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

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

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

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

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

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

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

9. 0.456 0.997 ↑ 1.0 1,492 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 75kB
10. 0.541 0.541 ↑ 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.541 rows=1,492 loops=1)

11. 0.297 0.989 ↓ 1.2 875 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
12. 0.478 0.692 ↓ 1.2 875 1

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

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

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

14. 0.032 0.082 ↑ 1.0 98 1

Hash (cost=3.32..3.32 rows=98 width=13) (actual time=0.082..0.082 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.009..0.050 rows=98 loops=1)

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

Hash (cost=162.73..162.73 rows=2,914 width=4) (actual time=2.978..2.978 rows=2,848 loops=1)

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

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

  • Filter: (NOT archived)
  • Rows Removed by Filter: 58
Planning time : 1.425 ms
Execution time : 139.480 ms