explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pDDl

Settings
# exclusive inclusive rows x rows loops node
1. 0.536 340.249 ↓ 52.9 476 1

Unique (cost=3,555.84..3,556.18 rows=9 width=143) (actual time=339.644..340.249 rows=476 loops=1)

2. 1.284 339.713 ↓ 71.7 645 1

Sort (cost=3,555.84..3,555.86 rows=9 width=143) (actual time=339.643..339.713 rows=645 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: 166kB
3. 142.060 338.429 ↓ 71.7 645 1

Hash Left Join (cost=1,528.55..3,555.70 rows=9 width=143) (actual time=25.888..338.429 rows=645 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 (products.internal_id = ANY ('{1,2}'::bigint[])) OR (defect_families.id IS NULL))
  • Rows Removed by Filter: 392970
4. 137.571 193.627 ↓ 6.8 393,615 1

Hash Left Join (cost=1,329.39..3,203.32 rows=58,210 width=164) (actual time=22.855..193.627 rows=393,615 loops=1)

  • Hash Cond: (product_groups.program_id = programs.id)
5. 13.449 55.028 ↓ 3.1 24,882 1

Hash Right Join (cost=1,291.10..2,644.13 rows=8,139 width=155) (actual time=21.813..55.028 rows=24,882 loops=1)

  • Hash Cond: (defect_family_products.defect_family_id = defect_families.id)
6. 14.446 22.875 ↑ 1.0 6,033 1

Hash Right Join (cost=180.74..1,429.76 rows=6,033 width=12) (actual time=3.083..22.875 rows=6,033 loops=1)

  • Hash Cond: (products.internal_id = defect_family_products.product_id)
7. 5.373 5.373 ↑ 1.0 35,555 1

Seq Scan on products (cost=0.00..877.10 rows=35,610 width=8) (actual time=0.009..5.373 rows=35,555 loops=1)

8. 1.630 3.056 ↑ 1.0 6,033 1

Hash (cost=105.33..105.33 rows=6,033 width=8) (actual time=3.055..3.056 rows=6,033 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 300kB
9. 1.426 1.426 ↑ 1.0 6,033 1

Seq Scan on defect_family_products (cost=0.00..105.33 rows=6,033 width=8) (actual time=0.014..1.426 rows=6,033 loops=1)

10. 3.782 18.704 ↓ 1.3 5,461 1

Hash (cost=1,059.54..1,059.54 rows=4,065 width=147) (actual time=18.704..18.704 rows=5,461 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 816kB
11. 2.327 14.922 ↓ 1.3 5,461 1

Hash Left Join (cost=216.36..1,059.54 rows=4,065 width=147) (actual time=4.424..14.922 rows=5,461 loops=1)

  • Hash Cond: (defect_family_ratings.product_group_id = product_groups.id)
12. 4.995 11.716 ↓ 1.3 5,461 1

Hash Right Join (cost=132.79..965.28 rows=4,065 width=147) (actual time=3.527..11.716 rows=5,461 loops=1)

  • Hash Cond: (defect_family_ratings.defect_family_id = defect_families.id)
13. 3.227 3.227 ↑ 1.0 18,889 1

Seq Scan on defect_family_ratings (cost=0.00..782.89 rows=18,889 width=8) (actual time=0.007..3.227 rows=18,889 loops=1)

14. 2.113 3.494 ↑ 1.0 3,013 1

Hash (cost=95.13..95.13 rows=3,013 width=143) (actual time=3.494..3.494 rows=3,013 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 460kB
15. 1.381 1.381 ↑ 1.0 3,013 1

Seq Scan on defect_families (cost=0.00..95.13 rows=3,013 width=143) (actual time=0.010..1.381 rows=3,013 loops=1)

16. 0.425 0.879 ↑ 1.0 1,492 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 75kB
17. 0.454 0.454 ↑ 1.0 1,492 1

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

18. 0.313 1.028 ↓ 1.2 875 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
19. 0.502 0.715 ↓ 1.2 875 1

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

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

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

21. 0.033 0.085 ↑ 1.0 98 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
22. 0.052 0.052 ↑ 1.0 98 1

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

  • Filter: active
  • Rows Removed by Filter: 34
23. 0.699 2.742 ↑ 1.0 2,848 1

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

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

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

  • Filter: (NOT archived)
  • Rows Removed by Filter: 58
Planning time : 1.754 ms
Execution time : 340.517 ms