explain.depesz.com

PostgreSQL's explain analyze made readable

Result: icYd

Settings
# exclusive inclusive rows x rows loops node
1. 1.210 217.500 ↑ 12.8 250 1

Sort (cost=100,074.79..100,082.80 rows=3,206 width=4,444) (actual time=217.457..217.500 rows=250 loops=1)

  • Sort Key: orders.table_branch, order_items.order_count, order_items.id, item_variations.id
  • Sort Method: quicksort Memory: 150kB
2. 0.144 216.290 ↑ 12.8 250 1

Hash Left Join (cost=59,831.53..93,759.60 rows=3,206 width=4,444) (actual time=196.675..216.290 rows=250 loops=1)

  • Hash Cond: (orders.id = epos_cat_authorize_sales.order_id)
3. 0.152 216.144 ↑ 12.8 250 1

Hash Left Join (cost=59,820.82..93,736.85 rows=3,206 width=3,323) (actual time=196.670..216.144 rows=250 loops=1)

  • Hash Cond: (orders.id = order_customers.order_id)
4. 0.145 215.991 ↑ 12.8 250 1

Hash Left Join (cost=59,792.82..93,380.14 rows=3,206 width=3,249) (actual time=196.664..215.991 rows=250 loops=1)

  • Hash Cond: (orders.id = servicecharges.order_id)
5. 17.972 215.840 ↑ 12.8 250 1

Hash Left Join (cost=59,778.60..93,305.78 rows=3,206 width=2,966) (actual time=196.649..215.840 rows=250 loops=1)

  • Hash Cond: (orders.id = taxes.order_id)
6. 12.276 168.153 ↑ 12.8 250 1

Hash Right Join (cost=56,517.76..86,962.99 rows=3,206 width=2,903) (actual time=166.861..168.153 rows=250 loops=1)

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

Seq Scan on pos_order child_orders (cost=0.00..6,451.03 rows=250,717 width=331) (actual time=0.008..51.413 rows=250,945 loops=1)

  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 5858
8. 0.680 104.464 ↑ 12.8 250 1

Hash (cost=55,459.68..55,459.68 rows=3,206 width=2,572) (actual time=104.464..104.464 rows=250 loops=1)

  • Buckets: 2048 Batches: 4 Memory Usage: 58kB
9. 0.277 103.784 ↑ 12.8 250 1

Nested Loop Left Join (cost=10,231.75..55,459.68 rows=3,206 width=2,572) (actual time=101.762..103.784 rows=250 loops=1)

10. 0.142 103.257 ↑ 12.8 250 1

Hash Left Join (cost=10,231.33..45,229.93 rows=3,206 width=2,453) (actual time=101.747..103.257 rows=250 loops=1)

  • Hash Cond: (discounts.id = discount_taxes.order_discount_id)
11. 0.115 102.578 ↑ 12.8 250 1

Hash Left Join (cost=10,167.96..45,143.85 rows=3,206 width=2,384) (actual time=101.204..102.578 rows=250 loops=1)

  • Hash Cond: (order_items.id = item_service_charges.order_item_id)
12. 0.102 102.461 ↑ 12.8 250 1

Hash Left Join (cost=10,152.11..45,047.84 rows=3,206 width=2,187) (actual time=101.199..102.461 rows=250 loops=1)

  • Hash Cond: (item_options.id = option_service_charges.order_item_id)
13. 0.124 102.355 ↑ 12.8 250 1

Hash Left Join (cost=10,136.26..44,951.83 rows=3,206 width=1,990) (actual time=101.192..102.355 rows=250 loops=1)

  • Hash Cond: (item_options.id = option_discounts.order_item_id)
14. 0.104 102.216 ↑ 12.8 250 1

Hash Left Join (cost=10,135.24..44,938.77 rows=3,206 width=1,807) (actual time=101.166..102.216 rows=250 loops=1)

  • Hash Cond: (orders.id = discounts.order_id)
15. 0.102 97.808 ↑ 12.8 250 1

Hash Left Join (cost=9,857.59..44,512.24 rows=3,206 width=1,569) (actual time=96.857..97.808 rows=250 loops=1)

  • Hash Cond: (order_items.id = item_discounts.order_item_id)
16. 0.032 97.686 ↑ 12.8 250 1

Nested Loop Left Join (cost=9,856.57..44,499.19 rows=3,206 width=1,386) (actual time=96.830..97.686 rows=250 loops=1)

17. 0.189 97.154 ↑ 12.8 250 1

Hash Right Join (cost=9,856.14..20,526.75 rows=3,206 width=1,328) (actual time=96.818..97.154 rows=250 loops=1)

  • Hash Cond: (order_items.order_id = orders.id)
18. 27.745 94.665 ↑ 9.8 385 1

Hash Right Join (cost=8,173.03..18,829.01 rows=3,775 width=989) (actual time=30.114..94.665 rows=385 loops=1)

  • Hash Cond: (item_variations.order_item_id = order_items.id)
19. 52.768 52.768 ↓ 1.0 392,935 1

Seq Scan on pos_order_item_variation item_variations (cost=0.00..9,179.02 rows=392,875 width=65) (actual time=0.012..52.768 rows=392,935 loops=1)

  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 1267
20. 0.216 14.152 ↑ 10.1 373 1

Hash (cost=8,125.84..8,125.84 rows=3,775 width=924) (actual time=14.152..14.152 rows=373 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 97kB
21. 0.170 13.936 ↑ 10.1 373 1

Hash Right Join (cost=6,704.22..8,125.84 rows=3,775 width=924) (actual time=13.792..13.936 rows=373 loops=1)

  • Hash Cond: (item_options.parent_id = order_items.id)
22. 0.005 12.084 ↑ 115.0 1 1

Nested Loop Left Join (cost=2,268.72..3,689.90 rows=115 width=491) (actual time=12.083..12.084 rows=1 loops=1)

23. 0.070 12.070 ↑ 115.0 1 1

Bitmap Heap Scan on pos_order_item item_options (cost=2,268.29..2,717.29 rows=115 width=433) (actual time=12.070..12.070 rows=1 loops=1)

  • Recheck Cond: ((bill_id IS NULL) AND (classification = 2))
  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 130
  • Heap Blocks: exact=47
24. 0.159 12.000 ↓ 0.0 0 1

BitmapAnd (cost=2,268.29..2,268.29 rows=115 width=0) (actual time=12.000..12.000 rows=0 loops=1)

25. 0.352 0.352 ↓ 1.4 5,285 1

Bitmap Index Scan on pos_order_item_bill_id_indx (cost=0.00..73.63 rows=3,893 width=0) (actual time=0.352..0.352 rows=5,285 loops=1)

  • Index Cond: (bill_id IS NULL)
26. 11.489 11.489 ↑ 1.0 116,994 1

Bitmap Index Scan on pos_order_classification_indx (cost=0.00..2,194.36 rows=118,657 width=0) (actual time=11.489..11.489 rows=116,994 loops=1)

  • Index Cond: (classification = 2)
27. 0.009 0.009 ↑ 1.0 1 1

Index Scan using pos_order_item_tax_order_item_id_indx on pos_order_item_tax option_taxes (cost=0.43..8.45 rows=1 width=58) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (order_item_id = item_options.id)
  • Filter: (NOT del_flg)
28. 0.081 1.682 ↑ 10.1 373 1

Hash (cost=4,388.31..4,388.31 rows=3,775 width=433) (actual time=1.682..1.682 rows=373 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 94kB
29. 1.601 1.601 ↑ 10.1 373 1

Index Scan using pos_order_item_bill_id_indx on pos_order_item order_items (cost=0.43..4,388.31 rows=3,775 width=433) (actual time=0.090..1.601 rows=373 loops=1)

  • Index Cond: (bill_id IS NULL)
  • Filter: ((NOT del_flg) AND (classification = 1))
  • Rows Removed by Filter: 4912
30. 0.026 2.300 ↑ 106.9 30 1

Hash (cost=1,643.04..1,643.04 rows=3,206 width=339) (actual time=2.300..2.300 rows=30 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 36kB
31. 0.026 2.274 ↑ 106.9 30 1

Hash Right Join (cost=4.46..1,643.04 rows=3,206 width=339) (actual time=2.251..2.274 rows=30 loops=1)

  • Hash Cond: (orders.table_id = t.id)
32. 2.215 2.215 ↑ 221.6 27 1

Index Scan using pos_order_payment_complete_indx on pos_order orders (cost=0.42..1,584.50 rows=5,984 width=331) (actual time=2.206..2.215 rows=27 loops=1)

  • Index Cond: (payment_complete = false)
  • Filter: ((NOT del_flg) AND (NOT payment_complete) AND (shop_id = 1))
  • Rows Removed by Filter: 5858
33. 0.008 0.033 ↑ 1.0 30 1

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

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

Seq Scan on pos_table t (cost=0.00..3.66 rows=30 width=8) (actual time=0.012..0.025 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
35. 0.500 0.500 ↑ 1.0 1 250

Index Scan using pos_order_item_tax_order_item_id_indx on pos_order_item_tax item_taxes (cost=0.43..7.47 rows=1 width=58) (actual time=0.001..0.002 rows=1 loops=250)

  • Index Cond: (order_item_id = order_items.id)
  • Filter: (NOT del_flg)
36. 0.010 0.020 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=183) (actual time=0.019..0.020 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on pos_order_item_discount item_discounts (cost=0.00..1.01 rows=1 width=183) (actual time=0.009..0.010 rows=1 loops=1)

  • Filter: (NOT del_flg)
38. 1.660 4.304 ↑ 1.0 4,860 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 705kB
39. 1.153 2.644 ↑ 1.0 4,860 1

Hash Right Join (cost=199.30..216.89 rows=4,860 width=238) (actual time=1.525..2.644 rows=4,860 loops=1)

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

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

  • Filter: (NOT del_flg)
41. 0.735 1.487 ↑ 1.0 4,860 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 702kB
42. 0.752 0.752 ↑ 1.0 4,860 1

Seq Scan on pos_order_discount discounts (cost=0.00..138.55 rows=4,860 width=91) (actual time=0.005..0.752 rows=4,860 loops=1)

  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 395
43. 0.004 0.015 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=183) (actual time=0.015..0.015 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on pos_order_item_discount option_discounts (cost=0.00..1.01 rows=1 width=183) (actual time=0.011..0.011 rows=1 loops=1)

  • Filter: (NOT del_flg)
45. 0.001 0.004 ↓ 0.0 0 1

Hash (cost=13.60..13.60 rows=180 width=197) (actual time=0.004..0.004 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
46. 0.003 0.003 ↓ 0.0 0 1

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

  • Filter: (NOT del_flg)
47. 0.002 0.002 ↓ 0.0 0 1

Hash (cost=13.60..13.60 rows=180 width=197) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
48. 0.000 0.000 ↓ 0.0 0 1

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

  • Filter: (NOT del_flg)
49. 0.270 0.537 ↑ 1.0 1,750 1

Hash (cost=41.50..41.50 rows=1,750 width=69) (actual time=0.537..0.537 rows=1,750 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 200kB
50. 0.267 0.267 ↑ 1.0 1,750 1

Seq Scan on pos_order_discount_tax discount_taxes (cost=0.00..41.50 rows=1,750 width=69) (actual time=0.004..0.267 rows=1,750 loops=1)

  • Filter: (NOT del_flg)
51. 0.250 0.250 ↑ 1.0 1 250

Index Scan using pos_customer_attribute_order_id_indx on pos_customer_attribute customer_attribute (cost=0.42..3.18 rows=1 width=119) (actual time=0.001..0.001 rows=1 loops=250)

  • Index Cond: (order_id = orders.id)
  • Filter: (NOT del_flg)
52. 20.098 29.715 ↑ 1.0 71,504 1

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

  • Buckets: 65536 (originally 65536) Batches: 4 (originally 2) Memory Usage: 3585kB
53. 9.617 9.617 ↑ 1.0 71,504 1

Seq Scan on pos_order_tax taxes (cost=0.00..1,598.04 rows=71,504 width=63) (actual time=0.010..9.617 rows=71,504 loops=1)

  • Filter: (NOT del_flg)
54. 0.002 0.006 ↓ 0.0 0 1

Hash (cost=12.60..12.60 rows=130 width=283) (actual time=0.005..0.006 rows=0 loops=1)

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

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

  • Filter: (NOT del_flg)
56. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=18.00..18.00 rows=800 width=74) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
57. 0.001 0.001 ↓ 0.0 0 1

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

58. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=10.70..10.70 rows=1 width=1,121) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
59. 0.002 0.002 ↓ 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,121) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((NOT del_flg) AND (bill_id IS NULL))