explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 54Ue : table data transaction V2

Settings
# exclusive inclusive rows x rows loops node
1. 144.021 12,990.759 ↑ 45.3 11,680 1

Unique (cost=1,068,030.69..1,071,998.55 rows=529,047 width=1,148) (actual time=12,466.301..12,990.759 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,926.935 12,846.729 ↑ 1.9 281,763 1

Sort (cost=1,068,019.82..1,069,342.44 rows=529,047 width=1,148) (actual time=12,466.298..12,846.729 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. 1,441.530 8,919.794 ↑ 1.9 281,763 1

Nested Loop Left Join (cost=10,433.51..486,089.87 rows=529,047 width=1,148) (actual time=186.333..8,919.794 rows=281,763 loops=1)

6. 18.551 371.728 ↓ 3.0 11,864 1

Hash Left Join (cost=10,433.08..15,656.02 rows=3,913 width=595) (actual time=178.385..371.728 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.750 352.138 ↓ 3.0 11,864 1

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

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

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

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

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

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

Hash Left Join (cost=9,752.04..14,878.82 rows=3,913 width=454) (actual time=166.804..282.852 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. 64.663 263.422 ↓ 3.0 11,864 1

Hash Left Join (cost=9,708.48..14,805.65 rows=3,913 width=302) (actual time=165.937..263.422 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.337 62.878 ↓ 3.0 11,680 1

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

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

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

  • Hash Cond: ((b.order_id)::text = (a.order_id)::text)
14. 4.162 4.162 ↓ 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..4.162 rows=11,852 loops=1)

15. 9.503 22.731 ↓ 3.0 8,776 1

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

  • Buckets: 16,384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2,951kB
16. 13.228 13.228 ↓ 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.046..13.228 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.205 6.665 ↓ 1.0 9,077 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 606kB
18. 3.460 3.460 ↓ 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.460 rows=9,077 loops=1)

19. 78.288 135.881 ↓ 1.0 166,303 1

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

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,006kB
20. 57.593 57.593 ↓ 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..57.593 rows=166,303 loops=1)

21. 0.188 0.838 ↑ 1.0 241 1

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

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

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

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

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

24. 0.178 0.328 ↑ 1.0 499 1

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

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

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

26. 4.500 8.507 ↑ 1.0 9,239 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 930kB
27. 4.007 4.007 ↑ 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.007 rows=9,239 loops=1)

28. 0.968 1.860 ↓ 1.0 2,075 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 235kB
29. 0.892 0.892 ↓ 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.892 rows=2,075 loops=1)

30. 0.005 0.013 ↓ 2.0 6 1

Hash (cost=1.03..1.03 rows=3 width=36) (actual time=0.013..0.013 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.502 1.039 ↑ 1.0 1,227 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 106kB
33. 0.537 0.537 ↑ 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..0.537 rows=1,227 loops=1)

34. 7,106.536 7,106.536 ↑ 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.324..0.599 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 : 5.283 ms
Execution time : 13,064.295 ms