explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FL13 : tableau_data_stock_v V1

Settings
# exclusive inclusive rows x rows loops node
1. 142.498 13,035.226 ↑ 45.3 11,680 1

Unique (cost=958,253.44..962,221.29 rows=529,047 width=1,148) (actual time=12,522.572..13,035.226 rows=11,680 loops=1)

2.          

Initplan (for Unique)

3. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on b2c_etc (cost=0.00..10.88 rows=1 width=516) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: ((type)::text = 'Distance'::text)
  • Rows Removed by Filter: 14
4. 3,845.554 12,892.719 ↑ 1.9 281,763 1

Sort (cost=958,242.57..959,565.18 rows=529,047 width=1,148) (actual time=12,522.568..12,892.719 rows=281,763 loops=1)

  • Sort Key: a.order_id, b.prod_code, h.date_time DESC
  • Sort Method: external merge Disk: 184,592kB
5. 810.249 9,047.165 ↑ 1.9 281,763 1

Nested Loop Left Join (cost=10,433.51..376,312.62 rows=529,047 width=1,148) (actual time=192.902..9,047.165 rows=281,763 loops=1)

6. 17.900 406.676 ↓ 3.0 11,864 1

Hash Left Join (cost=10,433.08..15,656.02 rows=3,913 width=595) (actual time=191.959..406.676 rows=11,864 loops=1)

  • Hash Cond: (((a.order_id)::text = (k.order_id)::text) AND ((a.shipping_no)::text = (k.order_no)::text))
7. 19.918 383.332 ↓ 3.0 11,864 1

Hash Left Join (cost=10,340.28..15,542.67 rows=3,913 width=581) (actual time=186.496..383.332 rows=11,864 loops=1)

  • Hash Cond: ((a.shipping_id)::integer = j.id)
8. 18.966 363.399 ↓ 3.0 11,864 1

Hash Left Join (cost=10,339.22..15,530.09 rows=3,913 width=551) (actual time=186.443..363.399 rows=11,864 loops=1)

  • Hash Cond: ((b.prod_code)::text = (f.prod_code)::text)
9. 20.334 342.525 ↓ 3.0 11,864 1

Hash Left Join (cost=10,138.55..15,319.13 rows=3,913 width=491) (actual time=184.504..342.525 rows=11,864 loops=1)

  • Hash Cond: ((a.username)::text = (e.username)::text)
10. 19.114 313.556 ↓ 3.0 11,864 1

Hash Left Join (cost=9,752.04..14,878.82 rows=3,913 width=454) (actual time=175.788..313.556 rows=11,864 loops=1)

  • Hash Cond: (((a.cust_number)::text = (c.cust_number)::text) AND ((a.shipto_number)::text = (c.shipto_number)::text))
11. 85.402 292.600 ↓ 3.0 11,864 1

Hash Left Join (cost=9,708.48..14,805.65 rows=3,913 width=302) (actual time=173.919..292.600 rows=11,864 loops=1)

  • Hash Cond: (((a.cust_number)::text = (i.cust_number)::text) AND ((a.shipto_number)::text = (i.shipto_number)::text) AND ((b.prod_code)::text = (i.prod_code_mh)::text))
12. 11.326 67.112 ↓ 3.0 11,680 1

Hash Left Join (cost=1,246.99..1,541.47 rows=3,913 width=294) (actual time=31.314..67.112 rows=11,680 loops=1)

  • Hash Cond: ((a.payment_type)::text = (g.header_id_payment)::text)
13. 18.136 48.991 ↓ 3.0 11,680 1

Hash Right Join (cost=721.99..1,006.21 rows=3,913 width=301) (actual time=24.434..48.991 rows=11,680 loops=1)

  • Hash Cond: ((b.order_id)::text = (a.order_id)::text)
14. 6.615 6.615 ↓ 1.0 11,852 1

Seq Scan on b2c_order_detail b (cost=0.00..253.39 rows=11,739 width=29) (actual time=0.011..6.615 rows=11,852 loops=1)

15. 10.835 24.240 ↓ 3.0 8,776 1

Hash (cost=684.82..684.82 rows=2,974 width=285) (actual time=24.240..24.240 rows=8,776 loops=1)

  • Buckets: 16,384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2,951kB
16. 13.405 13.405 ↓ 3.0 8,776 1

Seq Scan on b2c_order_header a (cost=0.00..684.82 rows=2,974 width=285) (actual time=0.041..13.405 rows=8,776 loops=1)

  • Filter: (to_char(order_date, 'YYYY-MM-DD'::text) >= '2019-11-01'::text)
  • Rows Removed by Filter: 130
17. 3.282 6.795 ↓ 1.0 9,077 1

Hash (cost=412.22..412.22 rows=9,022 width=21) (actual time=6.795..6.795 rows=9,077 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 606kB
18. 3.513 3.513 ↓ 1.0 9,077 1

Seq Scan on b2c_header_payment g (cost=0.00..412.22 rows=9,022 width=21) (actual time=0.005..3.513 rows=9,077 loops=1)

19. 78.752 140.086 ↓ 1.0 166,303 1

Hash (cost=4,418.27..4,418.27 rows=166,127 width=28) (actual time=140.086..140.086 rows=166,303 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,006kB
20. 61.334 61.334 ↓ 1.0 166,303 1

Seq Scan on b2c_mapping_apotek_uom i (cost=0.00..4,418.27 rows=166,127 width=28) (actual time=0.007..61.334 rows=166,303 loops=1)

21. 0.186 1.842 ↑ 1.0 241 1

Hash (cost=39.95..39.95 rows=241 width=166) (actual time=1.842..1.842 rows=241 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 56kB
22. 0.219 1.656 ↑ 1.0 241 1

Hash Left Join (cost=15.23..39.95 rows=241 width=166) (actual time=1.371..1.656 rows=241 loops=1)

  • Hash Cond: ((c.city_code)::text = (d.kab_id)::text)
23. 0.090 0.090 ↑ 1.0 241 1

Seq Scan on b2c_user_apotek c (cost=0.00..21.41 rows=241 width=156) (actual time=0.009..0.090 rows=241 loops=1)

24. 0.187 1.347 ↑ 1.0 499 1

Hash (cost=8.99..8.99 rows=499 width=20) (actual time=1.347..1.347 rows=499 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 34kB
25. 1.160 1.160 ↑ 1.0 499 1

Seq Scan on b2c_master_kab d (cost=0.00..8.99 rows=499 width=20) (actual time=0.548..1.160 rows=499 loops=1)

26. 4.592 8.635 ↑ 1.0 9,239 1

Hash (cost=270.67..270.67 rows=9,267 width=49) (actual time=8.634..8.635 rows=9,239 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 930kB
27. 4.043 4.043 ↑ 1.0 9,239 1

Seq Scan on b2c_user e (cost=0.00..270.67 rows=9,267 width=49) (actual time=0.007..4.043 rows=9,239 loops=1)

28. 1.001 1.908 ↓ 1.0 2,075 1

Hash (cost=174.74..174.74 rows=2,074 width=66) (actual time=1.908..1.908 rows=2,075 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 235kB
29. 0.907 0.907 ↓ 1.0 2,075 1

Seq Scan on b2c_master_product f (cost=0.00..174.74 rows=2,074 width=66) (actual time=0.007..0.907 rows=2,075 loops=1)

30. 0.007 0.015 ↓ 2.0 6 1

Hash (cost=1.03..1.03 rows=3 width=36) (actual time=0.015..0.015 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
31. 0.008 0.008 ↓ 2.0 6 1

Seq Scan on b2c_master_shipping j (cost=0.00..1.03 rows=3 width=36) (actual time=0.006..0.008 rows=6 loops=1)

32. 0.545 5.444 ↑ 1.0 1,227 1

Hash (cost=74.32..74.32 rows=1,232 width=42) (actual time=5.444..5.444 rows=1,227 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 106kB
33. 4.899 4.899 ↑ 1.0 1,227 1

Seq Scan on b2c_tracking_expedition k (cost=0.00..74.32 rows=1,232 width=42) (actual time=0.005..4.899 rows=1,227 loops=1)

34. 7,830.240 7,830.240 ↑ 1.6 24 11,864

Index Scan using b2c_user_log_user_id_idx on b2c_user_log h (cost=0.43..81.65 rows=38 width=41) (actual time=0.363..0.660 rows=24 loops=11,864)

  • Index Cond: ((a.username)::text = (user_id)::text)
  • Filter: ((date_time >= (a.order_date - '00:03:00'::interval)) AND (date_time <= (a.order_date + '00:03:00'::interval)))
  • Rows Removed by Filter: 1,264
Planning time : 11.382 ms
Execution time : 13,076.663 ms