explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xyW0

Settings
# exclusive inclusive rows x rows loops node
1. 4.708 14,406.164 ↓ 4.0 4 1

GroupAggregate (cost=8,400.82..8,400.84 rows=1 width=18) (actual time=14,402.054..14,406.164 rows=4 loops=1)

  • Group Key: od.deliverystatus
2. 5.444 14,401.456 ↓ 4,271.0 4,271 1

Sort (cost=8,400.82..8,400.82 rows=1 width=18) (actual time=14,401.298..14,401.456 rows=4,271 loops=1)

  • Sort Key: od.deliverystatus
  • Sort Method: quicksort Memory: 526kB
3. 17.840 14,396.012 ↓ 4,271.0 4,271 1

Nested Loop Left Join (cost=2,196.20..8,400.81 rows=1 width=18) (actual time=141.505..14,396.012 rows=4,271 loops=1)

  • Join Filter: (sp.orderdeliveryid = od.id)
  • Rows Removed by Join Filter: 46973
4. 3,539.885 14,373.901 ↓ 4,271.0 4,271 1

Nested Loop Left Join (cost=2,196.20..8,399.56 rows=1 width=18) (actual time=141.494..14,373.901 rows=4,271 loops=1)

  • Join Filter: (po_1.productid = pd.productid)
  • Rows Removed by Join Filter: 20287250
5. 2.239 9,279.372 ↓ 4,271.0 4,271 1

Nested Loop (cost=2,196.20..8,209.66 rows=1 width=26) (actual time=140.417..9,279.372 rows=4,271 loops=1)

6. 7.058 9,260.049 ↓ 4,271.0 4,271 1

Nested Loop (cost=2,195.92..8,209.31 rows=1 width=42) (actual time=140.406..9,260.049 rows=4,271 loops=1)

7. 2,856.104 9,231.571 ↓ 4,284.0 4,284 1

Nested Loop (cost=2,195.63..8,208.91 rows=1 width=42) (actual time=140.391..9,231.571 rows=4,284 loops=1)

  • Join Filter: (po.id = od.productorderid)
  • Rows Removed by Join Filter: 19180526
8. 11.300 238.002 ↓ 3,805.0 3,805 1

Hash Join (cost=2,195.63..7,943.83 rows=1 width=16) (actual time=138.039..238.002 rows=3,805 loops=1)

  • Hash Cond: ((pd.productid = po.productid) AND (q.quotationid = po.quotationid))
9. 7.729 221.869 ↓ 1,175.6 9,405 1

Nested Loop Left Join (cost=1,993.86..7,741.91 rows=8 width=24) (actual time=132.986..221.869 rows=9,405 loops=1)

10. 32.502 184.800 ↓ 1,222.5 4,890 1

Hash Join (cost=1,993.44..7,739.26 rows=4 width=24) (actual time=132.971..184.800 rows=4,890 loops=1)

  • Hash Cond: (q.productid = pd.productid)
11. 20.111 20.111 ↑ 1.0 128,348 1

Seq Scan on quotation q (cost=0.00..5,264.48 rows=128,348 width=34) (actual time=0.007..20.111 rows=128,348 loops=1)

12. 2.297 132.187 ↓ 2,930.0 2,930 1

Hash (cost=1,993.43..1,993.43 rows=1 width=8) (actual time=132.187..132.187 rows=2,930 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 118kB
13. 1.757 129.890 ↓ 2,930.0 2,930 1

Nested Loop (cost=425.00..1,993.43 rows=1 width=8) (actual time=23.661..129.890 rows=2,930 loops=1)

14. 6.021 63.673 ↓ 2,930.0 2,930 1

Nested Loop (cost=425.00..1,990.05 rows=1 width=14) (actual time=23.626..63.673 rows=2,930 loops=1)

15. 12.517 42.636 ↓ 3,754.0 3,754 1

Hash Right Join (cost=424.73..1,989.16 rows=1 width=23) (actual time=23.608..42.636 rows=3,754 loops=1)

  • Hash Cond: (sbd.productid = pd.productid)
16. 6.535 6.535 ↑ 1.0 31,813 1

Seq Scan on sourcerbriefingdetails sbd (cost=0.00..1,445.13 rows=31,813 width=37) (actual time=0.004..6.535 rows=31,813 loops=1)

17. 1.957 23.584 ↓ 3,754.0 3,754 1

Hash (cost=424.71..424.71 rows=1 width=23) (actual time=23.584..23.584 rows=3,754 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 202kB
18. 1.314 21.627 ↓ 3,754.0 3,754 1

Nested Loop (cost=176.87..424.71 rows=1 width=23) (actual time=2.214..21.627 rows=3,754 loops=1)

19. 0.156 0.193 ↓ 8.0 8 1

Bitmap Heap Scan on dbdepartment d (cost=4.37..38.07 rows=1 width=15) (actual time=0.067..0.193 rows=8 loops=1)

  • Recheck Cond: (clientid = 300000::numeric)
  • Filter: ((departmentid = ANY ('{5513074199,5513074201,5513074193,5513074209,5513074198,5513074198,5515014938,5513074215,5513074187}'::numeric[])) OR (parentdepartmentid = ANY ('{5513074199,5513074201,5513074193,5513074209,5513074198,5513074198,5515014938,5513074215,5513074187}'::numeric[])))
  • Rows Removed by Filter: 81
  • Heap Blocks: exact=4
20. 0.037 0.037 ↓ 8.1 89 1

Bitmap Index Scan on uxdepartment (cost=0.00..4.37 rows=11 width=0) (actual time=0.037..0.037 rows=89 loops=1)

  • Index Cond: (clientid = 300000::numeric)
21. 3.704 20.120 ↓ 6.3 469 8

Bitmap Heap Scan on product pd (cost=172.50..385.89 rows=75 width=32) (actual time=2.105..2.515 rows=469 loops=8)

  • Recheck Cond: ((department = d.departmentid) AND (client = 300000::numeric))
  • Heap Blocks: exact=2044
22. 0.296 16.416 ↓ 0.0 0 8

BitmapAnd (cost=172.50..172.50 rows=75 width=0) (actual time=2.052..2.052 rows=0 loops=8)

23. 1.168 1.168 ↓ 1.7 469 8

Bitmap Index Scan on ixfk_product_dept (cost=0.00..10.40 rows=282 width=0) (actual time=0.146..0.146 rows=469 loops=8)

  • Index Cond: (department = d.departmentid)
24. 14.952 14.952 ↓ 1.0 8,482 8

Bitmap Index Scan on ixfk_product_client (cost=0.00..159.72 rows=8,457 width=0) (actual time=1.869..1.869 rows=8,482 loops=8)

  • Index Cond: (client = 300000::numeric)
25. 15.016 15.016 ↑ 1.0 1 3,754

Index Scan using pk_ssnclnt on dbseasonclient sc (cost=0.28..0.88 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=3,754)

  • Index Cond: ((seasonclientid = pd.seasonclient) AND (seasonclientid = ANY ('{5513075494,5513075498,5513075500}'::numeric[])))
  • Filter: (clientid = 300000::numeric)
26. 64.460 64.460 ↑ 1.0 1 2,930

Seq Scan on dbclient c (cost=0.00..3.37 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=2,930)

  • Filter: ((clientid = 300000::numeric) AND ((companyshort)::text = 'ADH'::text))
  • Rows Removed by Filter: 90
27. 29.340 29.340 ↑ 1.5 2 4,890

Index Scan using ix_quotationitem_quotionid on quotationitem qi (cost=0.42..0.63 rows=3 width=17) (actual time=0.005..0.006 rows=2 loops=4,890)

  • Index Cond: (q.quotationid = quotationid)
28. 2.996 4.833 ↑ 1.0 4,751 1

Hash (cost=130.51..130.51 rows=4,751 width=26) (actual time=4.833..4.833 rows=4,751 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 274kB
29. 1.837 1.837 ↑ 1.0 4,751 1

Seq Scan on productorder po (cost=0.00..130.51 rows=4,751 width=26) (actual time=0.009..1.837 rows=4,751 loops=1)

30. 6,137.465 6,137.465 ↑ 1.0 5,042 3,805

Seq Scan on orderdelivery od (cost=0.00..202.05 rows=5,042 width=26) (actual time=0.002..1.613 rows=5,042 loops=3,805)

  • Filter: (isarchived IS FALSE)
  • Rows Removed by Filter: 2663
31. 21.420 21.420 ↑ 1.0 1 4,284

Index Only Scan using deliveryinfo_pkey on deliveryinfo dpi (cost=0.28..0.39 rows=1 width=9) (actual time=0.005..0.005 rows=1 loops=4,284)

  • Index Cond: (orderdeliveryid = od.id)
  • Heap Fetches: 4271
32. 17.084 17.084 ↑ 1.0 1 4,271

Index Only Scan using orderinfo_pkey on orderinfo od2 (cost=0.28..0.34 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=4,271)

  • Index Cond: (productorderid = od.productorderid)
  • Heap Fetches: 4271
33. 1,554.644 1,554.644 ↑ 1.0 4,751 4,271

Seq Scan on productorder po_1 (cost=0.00..130.51 rows=4,751 width=9) (actual time=0.002..0.364 rows=4,751 loops=4,271)

34. 4.271 4.271 ↑ 1.0 11 4,271

Seq Scan on supplierpenalty sp (cost=0.00..1.11 rows=11 width=9) (actual time=0.001..0.001 rows=11 loops=4,271)

Planning time : 12.387 ms
Execution time : 14,406.726 ms