explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pnPL

Settings
# exclusive inclusive rows x rows loops node
1. 15.575 2,097.851 ↓ 3,505.0 3,505 1

Sort (cost=92,162.55..92,162.56 rows=1 width=378) (actual time=2,097.362..2,097.851 rows=3,505 loops=1)

  • Sort Key: dd.defect_count DESC, dd.inspection_count DESC, dd.defect_code_id DESC, drdco.code
  • Sort Method: quicksort Memory: 1,076kB
2.          

CTE inspections

3. 34.979 547.111 ↓ 59,442.0 59,442 1

WindowAgg (cost=0.01..92,020.90 rows=1 width=40) (actual time=531.057..547.111 rows=59,442 loops=1)

4. 7.774 512.132 ↓ 59,442.0 59,442 1

Result (cost=0.01..92,020.89 rows=1 width=16) (actual time=0.090..512.132 rows=59,442 loops=1)

  • One-Time Filter: ((('{252437,252437}'::text[])[2] = '252437'::text) AND (('{0}'::integer[])[1] = 0))
5. 504.358 504.358 ↓ 59,442.0 59,442 1

Seq Scan on fr_inspections_full i (cost=0.01..92,020.89 rows=1 width=16) (actual time=0.085..504.358 rows=59,442 loops=1)

  • Filter: (((('{0}'::integer[])[1] = 0) OR (retailer_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (office_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (executor_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (supplier_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (factory_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (country_of_factory_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (product_line_ids && '{0}'::integer[])) AND ((('{0}'::integer[])[1] = 0) OR (product_category_ids && '{0}'::integer[])) AND ((('{0}'::integer[])[1] = 0) OR (brand_ids && '{0}'::integer[])) AND ((('{0}'::integer[])[1] = 0) OR (item_ids && '{0}'::integer[])) AND ((('{0}'::integer[])[1] = 0) OR (inspection_type_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (po_ids && '{0}'::integer[])) AND ((('{0}'::integer[])[1] = 0) OR (po_type_ids && '{0}'::integer[])) AND (inspection_date >= to_timestamp(((('{1564617600000,1596239999999}'::bigint[])[1] / 1000))::double precision)) AND (inspection_date <= to_timestamp(((('{1564617600000,1596239999999}'::bigint[])[2] / 1000))::double precision)) AND ((('{252437,252437}'::text[])[2])::integer = ANY (involved_org_ids)))
  • Rows Removed by Filter: 160,010
6.          

CTE defects

7. 229.837 1,883.926 ↓ 133,284.0 133,284 1

GroupAggregate (cost=108.41..108.45 rows=1 width=72) (actual time=1,606.093..1,883.926 rows=133,284 loops=1)

  • Group Key: d.defect_code_id, d.defect_category_id, d.booking_id, d.defect_classification_id, d.defect_group_id
8. 291.453 1,654.089 ↓ 234,624.0 234,624 1

Sort (cost=108.41..108.42 rows=1 width=68) (actual time=1,606.051..1,654.089 rows=234,624 loops=1)

  • Sort Key: d.defect_code_id, d.defect_category_id, d.booking_id, d.defect_classification_id, d.defect_group_id
  • Sort Method: external merge Disk: 13,080kB
9. 146.964 1,362.636 ↓ 234,624.0 234,624 1

Nested Loop (cost=0.43..108.40 rows=1 width=68) (actual time=531.367..1,362.636 rows=234,624 loops=1)

10. 561.810 561.810 ↓ 59,442.0 59,442 1

CTE Scan on inspections i_1 (cost=0.00..0.02 rows=1 width=40) (actual time=531.070..561.810 rows=59,442 loops=1)

11. 653.862 653.862 ↓ 4.0 4 59,442

Index Scan using idx_fr_inspection_defects_booking_id on fr_inspection_defects d (cost=0.43..108.37 rows=1 width=36) (actual time=0.008..0.011 rows=4 loops=59,442)

  • Index Cond: (booking_id = i_1.booking_id)
  • Filter: (((('{0}'::integer[])[1] = 0) OR (product_line_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (product_category_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (brand_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (item_id = 0)) AND ((('{0}'::integer[])[1] = 0) OR (inspection_type_id = 0)))
12. 8.532 2,082.276 ↓ 3,505.0 3,505 1

Nested Loop Left Join (cost=0.82..33.19 rows=1 width=378) (actual time=2,056.076..2,082.276 rows=3,505 loops=1)

13. 1.839 2,070.239 ↓ 3,505.0 3,505 1

Nested Loop Left Join (cost=0.55..24.85 rows=1 width=232) (actual time=2,056.002..2,070.239 rows=3,505 loops=1)

14. 0.856 2,061.390 ↓ 3,505.0 3,505 1

Nested Loop Left Join (cost=0.26..16.53 rows=1 width=169) (actual time=2,055.987..2,061.390 rows=3,505 loops=1)

15. 1.023 2,057.029 ↓ 3,505.0 3,505 1

Hash Right Join (cost=0.11..8.35 rows=1 width=158) (actual time=2,055.966..2,057.029 rows=3,505 loops=1)

  • Hash Cond: (drdcl.id = dd.defect_classification_id)
16. 0.099 0.099 ↓ 1.1 349 1

Seq Scan on dr_defect_classification drdcl (cost=0.00..7.03 rows=322 width=10) (actual time=0.030..0.099 rows=349 loops=1)

  • Filter: (status = 1)
17. 0.755 2,055.907 ↓ 3,505.0 3,505 1

Hash (cost=0.10..0.10 rows=1 width=152) (actual time=2,055.907..2,055.907 rows=3,505 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 313kB
18. 0.386 2,055.152 ↓ 3,505.0 3,505 1

Subquery Scan on dd (cost=0.03..0.10 rows=1 width=152) (actual time=2,053.981..2,055.152 rows=3,505 loops=1)

19. 3.139 2,054.766 ↓ 3,505.0 3,505 1

WindowAgg (cost=0.03..0.09 rows=1 width=152) (actual time=2,053.980..2,054.766 rows=3,505 loops=1)

20. 56.594 2,051.627 ↓ 3,505.0 3,505 1

GroupAggregate (cost=0.03..0.07 rows=1 width=120) (actual time=1,984.571..2,051.627 rows=3,505 loops=1)

  • Group Key: x.defect_code_id, x.defect_category_id, x.defect_classification_id, x.defect_group_id
21. 70.129 1,995.033 ↓ 133,284.0 133,284 1

Sort (cost=0.03..0.04 rows=1 width=72) (actual time=1,984.539..1,995.033 rows=133,284 loops=1)

  • Sort Key: x.defect_code_id, x.defect_category_id, x.defect_classification_id, x.defect_group_id
  • Sort Method: quicksort Memory: 14,194kB
22. 1,924.904 1,924.904 ↓ 133,284.0 133,284 1

CTE Scan on defects x (cost=0.00..0.02 rows=1 width=72) (actual time=1,606.097..1,924.904 rows=133,284 loops=1)

23. 3.505 3.505 ↑ 1.0 1 3,505

Index Scan using idx_dr_defect_group_id on dr_defect_group drdgr (cost=0.15..8.17 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=3,505)

  • Index Cond: (dd.defect_group_id = id)
  • Filter: (status = 1)
24. 7.010 7.010 ↑ 1.0 1 3,505

Index Scan using idx_dr_defect_code_id on dr_defect_code drdco (cost=0.29..8.31 rows=1 width=67) (actual time=0.002..0.002 rows=1 loops=3,505)

  • Index Cond: (dd.defect_code_id = id)
  • Filter: (status = 1)
25. 3.505 3.505 ↑ 1.0 1 3,505

Index Scan using idx_dr_defect_category_id on dr_defect_category drdca (cost=0.28..8.30 rows=1 width=27) (actual time=0.001..0.001 rows=1 loops=3,505)

  • Index Cond: (dd.defect_category_id = id)
  • Filter: (status = 1)
Planning time : 2.262 ms
Execution time : 2,105.886 ms