explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NDdY

Settings
# exclusive inclusive rows x rows loops node
1. 6.957 707.652 ↑ 13.2 240 1

Sort (cost=100,180.89..100,188.80 rows=3,162 width=3,996) (actual time=707.613..707.652 rows=240 loops=1)

  • Sort Key: orders.table_branch, order_items.order_count, order_items.id, item_variations.id
  • Sort Method: quicksort Memory: 148kB
2. 0.597 700.695 ↑ 13.2 240 1

Hash Left Join (cost=62,742.59..94,558.08 rows=3,162 width=3,996) (actual time=663.211..700.695 rows=240 loops=1)

  • Hash Cond: (orders.id = epos_cat_authorize_sales.order_id)
3. 0.558 700.093 ↑ 13.2 240 1

Hash Left Join (cost=62,731.87..94,535.50 rows=3,162 width=2,907) (actual time=663.197..700.093 rows=240 loops=1)

  • Hash Cond: (orders.id = order_customers.order_id)
4. 0.532 699.530 ↑ 13.2 240 1

Hash Left Join (cost=62,703.87..94,183.29 rows=3,162 width=2,857) (actual time=663.184..699.530 rows=240 loops=1)

  • Hash Cond: (orders.id = servicecharges.order_id)
5. 33.351 698.989 ↑ 13.2 240 1

Hash Left Join (cost=62,689.65..94,109.76 rows=3,162 width=2,598) (actual time=663.159..698.989 rows=240 loops=1)

  • Hash Cond: (orders.id = taxes.order_id)
6. 33.678 548.549 ↑ 13.2 240 1

Hash Right Join (cost=59,638.81..88,483.26 rows=3,162 width=2,559) (actual time=545.806..548.549 rows=240 loops=1)

  • Hash Cond: (child_orders.parent_id = orders.id)
7. 187.074 187.074 ↓ 1.0 250,945 1

Seq Scan on pos_order child_orders (cost=0.00..6,451.03 rows=250,777 width=307) (actual time=0.015..187.074 rows=250,945 loops=1)

  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 5858
8. 1.296 327.797 ↑ 13.2 240 1

Hash (cost=58,718.28..58,718.28 rows=3,162 width=2,252) (actual time=327.797..327.797 rows=240 loops=1)

  • Buckets: 2048 Batches: 4 Memory Usage: 47kB
9. 0.516 326.501 ↑ 13.2 240 1

Nested Loop Left Join (cost=21,414.76..58,718.28 rows=3,162 width=2,252) (actual time=320.218..326.501 rows=240 loops=1)

10. 0.406 324.545 ↑ 13.2 240 1

Hash Left Join (cost=21,414.34..46,446.84 rows=3,162 width=2,157) (actual time=320.193..324.545 rows=240 loops=1)

  • Hash Cond: (discounts.id = discount_taxes.order_discount_id)
11. 0.397 320.628 ↑ 13.2 240 1

Hash Left Join (cost=21,350.97..46,361.08 rows=3,162 width=2,120) (actual time=316.670..320.628 rows=240 loops=1)

  • Hash Cond: (order_items.id = item_service_charges.order_item_id)
12. 0.365 320.226 ↑ 13.2 240 1

Hash Left Join (cost=21,335.12..46,266.17 rows=3,162 width=1,947) (actual time=316.651..320.226 rows=240 loops=1)

  • Hash Cond: (item_options.id = option_service_charges.order_item_id)
13. 0.354 319.853 ↑ 13.2 240 1

Hash Left Join (cost=21,319.27..46,171.26 rows=3,162 width=1,782) (actual time=316.630..319.853 rows=240 loops=1)

  • Hash Cond: (item_options.id = option_discounts.order_item_id)
14. 0.378 319.465 ↑ 13.2 240 1

Hash Left Join (cost=21,303.09..46,072.07 rows=3,162 width=1,623) (actual time=316.573..319.465 rows=240 loops=1)

  • Hash Cond: (orders.id = discounts.order_id)
15. 0.342 300.994 ↑ 13.2 240 1

Hash Left Join (cost=21,025.45..45,647.58 rows=3,162 width=1,425) (actual time=298.463..300.994 rows=240 loops=1)

  • Hash Cond: (order_items.id = item_discounts.order_item_id)
16. 0.323 300.629 ↑ 13.2 240 1

Nested Loop Left Join (cost=21,009.28..45,548.40 rows=3,162 width=1,266) (actual time=298.423..300.629 rows=240 loops=1)

17. 0.464 298.866 ↑ 13.2 240 1

Hash Right Join (cost=21,008.85..22,918.39 rows=3,162 width=1,232) (actual time=298.413..298.866 rows=240 loops=1)

  • Hash Cond: (item_options.parent_id = order_items.id)
18. 0.024 37.980 ↑ 155.0 1 1

Nested Loop Left Join (cost=2,205.63..4,114.59 rows=155 width=451) (actual time=37.977..37.980 rows=1 loops=1)

  • -> Index Scan using pos_order_item_tax_order_item_id_indx on pos_order_item_tax option_taxes (cost=0.43..8.42 rows=1 width=34) (actual time=0.015..0.015 row (...)
19. 0.821 37.956 ↑ 155.0 1 1

Bitmap Heap Scan on pos_order_item item_options (cost=2,205.20..2,807.67 rows=155 width=417) (actual time=37.954..37.956 rows=1 loops=1)

  • Recheck Cond: ((bill_id IS NULL) AND (classification = 2))
  • Rows Removed by Index Recheck: 867
  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 130
  • Heap Blocks: exact=279
  • Index Cond: (order_item_id = item_options.id)
  • Filter: (NOT del_flg)
20. 0.448 37.135 ↓ 0.0 0 1

BitmapAnd (cost=2,205.20..2,205.20 rows=155 width=0) (actual time=37.135..37.135 rows=0 loops=1)

21. 0.900 0.900 ↑ 1.0 5,285 1

Bitmap Index Scan on pos_order_item_bill_id_indx (cost=0.00..105.71 rows=5,504 width=0) (actual time=0.900..0.900 rows=5,285 loops=1)

  • Index Cond: (bill_id IS NULL)
22. 35.787 35.787 ↓ 1.0 116,994 1

Bitmap Index Scan on pos_order_classification_indx (cost=0.00..2,099.16 rows=113,431 width=0) (actual time=35.787..35.787 rows=116,994 loops=1)

  • Index Cond: (classification = 2)
23. 0.400 260.422 ↑ 13.2 240 1

Hash (cost=18,763.69..18,763.69 rows=3,162 width=781) (actual time=260.422..260.422 rows=240 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 86kB
24. 0.333 260.022 ↑ 13.2 240 1

Hash Right Join (cost=8,085.69..18,763.69 rows=3,162 width=781) (actual time=259.312..260.022 rows=240 loops=1)

  • Hash Cond: (order_items.order_id = orders.id)
25. 247.746 253.661 ↑ 13.9 385 1

Hash Right Join (cost=6,461.82..17,119.13 rows=5,342 width=466) (actual time=56.582..253.661 rows=385 loops=1)

  • Hash Cond: (item_variations.order_item_id = order_items.id)
  • -> Seq Scan on pos_order_item_variation item_variations (cost=0.00..9179.02 rows=392822 width=49) (actual time=0.020..165.997 rows=392935 loops= (...)
  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 1267
26. 5.915 5.915 ↑ 14.3 373 1

Hash (cost=6,395.04..6,395.04 rows=5,342 width=417) (actual time=5.915..5.915 rows=373 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 120kB
  • -> Index Scan using pos_order_item_bill_id_indx on pos_order_item order_items (cost=0.43..6395.04 rows=5342 width=417) (actual time=0.023. (...)
  • Index Cond: (bill_id IS NULL)
  • Filter: ((NOT del_flg) AND (classification = 1))
  • Rows Removed by Filter: 4912
27. 0.022 6.028 ↑ 158.1 20 1

Hash (cost=1,584.34..1,584.34 rows=3,162 width=315) (actual time=6.028..6.028 rows=20 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 35kB
28. 5.922 6.006 ↑ 158.1 20 1

Hash Join (cost=4.46..1,584.34 rows=3,162 width=315) (actual time=5.966..6.006 rows=20 loops=1)

  • Hash Cond: (orders.table_id = t.id)
  • -> Index Scan using pos_order_payment_complete_indx on pos_order orders (cost=0.42..1526.55 rows=5902 width=315) (actual time=5.853..5.873 (...)
  • Index Cond: (payment_complete = false)
  • Filter: ((NOT del_flg) AND (NOT payment_complete) AND (shop_id = 1))
  • Rows Removed by Filter: 5858
29. 0.011 0.084 ↑ 1.0 30 1

Hash (cost=3.66..3.66 rows=30 width=8) (actual time=0.084..0.084 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
30. 0.073 0.073 ↑ 1.0 30 1

Seq Scan on pos_table t (cost=0.00..3.66 rows=30 width=8) (actual time=0.029..0.073 rows=30 loops=1)

  • Filter: (id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,39,38,40,41,42,43,44,15,16,17,18,19,20,25,26,23}'::bigint[]))
  • Rows Removed by Filter: 26
31. 1.440 1.440 ↑ 1.0 1 240

Index Scan using pos_order_item_tax_order_item_id_indx on pos_order_item_tax item_taxes (cost=0.43..7.15 rows=1 width=34) (actual time=0.005..0.006 rows=1 loops=240)

  • Index Cond: (order_item_id = order_items.id)
  • Filter: (NOT del_flg)
32. 0.005 0.023 ↑ 190.0 1 1

Hash (cost=13.80..13.80 rows=190 width=159) (actual time=0.023..0.023 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.018 0.018 ↑ 190.0 1 1

Seq Scan on pos_order_item_discount item_discounts (cost=0.00..13.80 rows=190 width=159) (actual time=0.017..0.018 rows=1 loops=1)

  • Filter: (NOT del_flg)
34. 5.071 18.093 ↑ 1.0 4,860 1

Hash (cost=216.89..216.89 rows=4,860 width=198) (actual time=18.093..18.093 rows=4,860 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 638kB
35. 3.834 13.022 ↑ 1.0 4,860 1

Hash Right Join (cost=199.30..216.89 rows=4,860 width=198) (actual time=9.200..13.022 rows=4,860 loops=1)

  • Hash Cond: (discount_service_charges.order_discount_id = discounts.id)
36. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on pos_order_discount_service_charge discount_service_charges (cost=0.00..14.60 rows=230 width=123) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: (NOT del_flg)
37. 4.601 9.180 ↑ 1.0 4,860 1

Hash (cost=138.55..138.55 rows=4,860 width=75) (actual time=9.180..9.180 rows=4,860 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 635kB
38. 4.579 4.579 ↑ 1.0 4,860 1

Seq Scan on pos_order_discount discounts (cost=0.00..138.55 rows=4,860 width=75) (actual time=0.013..4.579 rows=4,860 loops=1)

  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 395
39. 0.005 0.034 ↑ 190.0 1 1

Hash (cost=13.80..13.80 rows=190 width=159) (actual time=0.034..0.034 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.029 0.029 ↑ 190.0 1 1

Seq Scan on pos_order_item_discount option_discounts (cost=0.00..13.80 rows=190 width=159) (actual time=0.027..0.029 rows=1 loops=1)

  • Filter: (NOT del_flg)
41. 0.001 0.008 ↓ 0.0 0 1

Hash (cost=13.60..13.60 rows=180 width=173) (actual time=0.008..0.008 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
42. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on pos_order_item_service_charge option_service_charges (cost=0.00..13.60 rows=180 width=173) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: (NOT del_flg)
43. 0.000 0.005 ↓ 0.0 0 1

Hash (cost=13.60..13.60 rows=180 width=173) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
44. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on pos_order_item_service_charge item_service_charges (cost=0.00..13.60 rows=180 width=173) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: (NOT del_flg)
45. 1.744 3.511 ↑ 1.0 1,750 1

Hash (cost=41.50..41.50 rows=1,750 width=45) (actual time=3.511..3.511 rows=1,750 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 156kB
46. 1.767 1.767 ↑ 1.0 1,750 1

Seq Scan on pos_order_discount_tax discount_taxes (cost=0.00..41.50 rows=1,750 width=45) (actual time=0.018..1.767 rows=1,750 loops=1)

  • Filter: (NOT del_flg)
47. 1.440 1.440 ↑ 1.0 1 240

Index Scan using pos_customer_attribute_order_id_indx on pos_customer_attribute customer_attribute (cost=0.42..3.87 rows=1 width=95) (actual time=0.005..0.006 rows=1 loops=240)

  • Index Cond: (order_id = orders.id)
  • Filter: (NOT del_flg)
48. 68.963 117.089 ↑ 1.0 71,504 1

Hash (cost=1,598.04..1,598.04 rows=71,504 width=39) (actual time=117.089..117.089 rows=71,504 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 1798kB
49. 48.126 48.126 ↑ 1.0 71,504 1

Seq Scan on pos_order_tax taxes (cost=0.00..1,598.04 rows=71,504 width=39) (actual time=0.022..48.126 rows=71,504 loops=1)

  • Filter: (NOT del_flg)
50. 0.001 0.009 ↓ 0.0 0 1

Hash (cost=12.60..12.60 rows=130 width=259) (actual time=0.009..0.009 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
51. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on pos_order_service_charge servicecharges (cost=0.00..12.60 rows=130 width=259) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: (NOT del_flg)
52. 0.000 0.005 ↓ 0.0 0 1

Hash (cost=18.00..18.00 rows=800 width=50) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
53. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on pos_order_customer order_customers (cost=0.00..18.00 rows=800 width=50) (actual time=0.005..0.005 rows=0 loops=1)

54. 0.000 0.005 ↓ 0.0 0 1

Hash (cost=10.70..10.70 rows=1 width=1,097) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
55. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on pos_epos_cat_authorize_sales epos_cat_authorize_sales (cost=0.00..10.70 rows=1 width=1,097) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: ((NOT del_flg) AND (bill_id IS NULL))
Planning time : 22.369 ms
Execution time : 710.584 ms