explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q4Xy

Settings
# exclusive inclusive rows x rows loops node
1. 3.084 707.094 ↑ 12.6 250 1

Sort (cost=103,770.77..103,778.67 rows=3,162 width=4,444) (actual time=707.048..707.094 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.779 704.010 ↑ 12.6 250 1

Hash Left Join (cost=63,657.96..97,542.45 rows=3,162 width=4,444) (actual time=664.878..704.010 rows=250 loops=1)

  • Hash Cond: (orders.id = epos_cat_authorize_sales.order_id)
3. 0.774 703.226 ↑ 12.6 250 1

Hash Left Join (cost=63,647.25..97,519.87 rows=3,162 width=3,323) (actual time=664.859..703.226 rows=250 loops=1)

  • Hash Cond: (orders.id = order_customers.order_id)
4. 0.768 702.446 ↑ 12.6 250 1

Hash Left Join (cost=63,619.25..97,167.67 rows=3,162 width=3,249) (actual time=664.839..702.446 rows=250 loops=1)

  • Hash Cond: (orders.id = servicecharges.order_id)
5. 32.295 701.667 ↑ 12.6 250 1

Hash Left Join (cost=63,605.02..97,094.13 rows=3,162 width=2,966) (actual time=664.804..701.667 rows=250 loops=1)

  • Hash Cond: (orders.id = taxes.order_id)
6. 36.825 582.985 ↑ 12.6 250 1

Hash Right Join (cost=60,344.18..90,781.64 rows=3,162 width=2,903) (actual time=578.366..582.985 rows=250 loops=1)

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

Seq Scan on pos_order child_orders (cost=0.00..6,451.03 rows=250,777 width=331) (actual time=0.018..203.162 rows=250,945 loops=1)

  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 5858
8. 2.057 342.998 ↑ 12.6 250 1

Hash (cost=59,300.66..59,300.66 rows=3,162 width=2,572) (actual time=342.998..342.998 rows=250 loops=1)

  • Buckets: 1024 Batches: 4 Memory Usage: 56kB
9. 0.820 340.941 ↑ 12.6 250 1

Nested Loop Left Join (cost=12,608.13..59,300.66 rows=3,162 width=2,572) (actual time=331.222..340.941 rows=250 loops=1)

10. 0.598 338.371 ↑ 12.6 250 1

Hash Left Join (cost=12,607.71..47,029.21 rows=3,162 width=2,453) (actual time=331.195..338.371 rows=250 loops=1)

  • Hash Cond: (discounts.id = discount_taxes.order_discount_id)
11. 0.581 336.464 ↑ 12.6 250 1

Hash Left Join (cost=12,544.33..46,943.45 rows=3,162 width=2,384) (actual time=329.876..336.464 rows=250 loops=1)

  • Hash Cond: (order_items.id = item_service_charges.order_item_id)
12. 0.516 335.879 ↑ 12.6 250 1

Hash Left Join (cost=12,528.48..46,848.54 rows=3,162 width=2,187) (actual time=329.862..335.879 rows=250 loops=1)

  • Hash Cond: (item_options.id = option_service_charges.order_item_id)
13. 0.524 335.357 ↑ 12.6 250 1

Hash Left Join (cost=12,512.63..46,753.63 rows=3,162 width=1,990) (actual time=329.847..335.357 rows=250 loops=1)

  • Hash Cond: (item_options.id = option_discounts.order_item_id)
14. 0.639 334.800 ↑ 12.6 250 1

Hash Left Join (cost=12,496.46..46,654.44 rows=3,162 width=1,807) (actual time=329.795..334.800 rows=250 loops=1)

  • Hash Cond: (orders.id = discounts.order_id)
15. 0.469 321.389 ↑ 12.6 250 1

Hash Left Join (cost=12,218.81..46,229.96 rows=3,162 width=1,569) (actual time=317.009..321.389 rows=250 loops=1)

  • Hash Cond: (order_items.id = item_discounts.order_item_id)
16. 0.657 320.900 ↑ 12.6 250 1

Nested Loop Left Join (cost=12,202.64..46,130.77 rows=3,162 width=1,386) (actual time=316.973..320.900 rows=250 loops=1)

17. 0.412 318.493 ↑ 12.6 250 1

Hash Left Join (cost=12,202.21..23,500.77 rows=3,162 width=1,328) (actual time=316.962..318.493 rows=250 loops=1)

  • Hash Cond: (order_items.id = item_options.parent_id)
18. 0.540 280.591 ↑ 12.6 250 1

Hash Right Join (cost=8,085.69..18,763.69 rows=3,162 width=837) (actual time=279.450..280.591 rows=250 loops=1)

  • Hash Cond: (order_items.order_id = orders.id)
19. 88.934 273.781 ↑ 13.9 385 1

Hash Right Join (cost=6,461.82..17,119.13 rows=5,342 width=498) (actual time=62.519..273.781 rows=385 loops=1)

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

Seq Scan on pos_order_item_variation item_variations (cost=0.00..9,179.02 rows=392,822 width=65) (actual time=0.025..179.347 rows=392,935 loops=1)

  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 1267
21. 0.141 5.500 ↑ 14.3 373 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 126kB
22. 5.359 5.359 ↑ 14.3 373 1

Index Scan using pos_order_item_bill_id_indx on pos_order_item order_items (cost=0.43..6,395.04 rows=5,342 width=433) (actual time=0.021..5.359 rows=373 loops=1)

  • Index Cond: (bill_id IS NULL)
  • Filter: ((NOT del_flg) AND (classification = 1))
  • Rows Removed by Filter: 4912
23. 0.028 6.270 ↑ 105.4 30 1

Hash (cost=1,584.34..1,584.34 rows=3,162 width=339) (actual time=6.270..6.270 rows=30 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 36kB
24. 0.056 6.242 ↑ 105.4 30 1

Hash Right Join (cost=4.46..1,584.34 rows=3,162 width=339) (actual time=6.180..6.242 rows=30 loops=1)

  • Hash Cond: (orders.table_id = t.id)
25. 6.097 6.097 ↑ 218.6 27 1

Index Scan using pos_order_payment_complete_indx on pos_order orders (cost=0.42..1,526.55 rows=5,902 width=331) (actual time=6.080..6.097 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
26. 0.013 0.089 ↑ 1.0 30 1

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

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

Seq Scan on pos_table t (cost=0.00..3.66 rows=30 width=8) (actual time=0.030..0.076 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
28. 0.009 37.490 ↑ 155.0 1 1

Hash (cost=4,114.59..4,114.59 rows=155 width=491) (actual time=37.490..37.490 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.010 37.481 ↑ 155.0 1 1

Nested Loop Left Join (cost=2,205.63..4,114.59 rows=155 width=491) (actual time=37.479..37.481 rows=1 loops=1)

30. 0.821 37.456 ↑ 155.0 1 1

Bitmap Heap Scan on pos_order_item item_options (cost=2,205.20..2,807.67 rows=155 width=433) (actual time=37.455..37.456 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
31. 0.480 36.635 ↓ 0.0 0 1

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

32. 1.177 1.177 ↑ 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=1.177..1.177 rows=5,285 loops=1)

  • Index Cond: (bill_id IS NULL)
33. 34.978 34.978 ↓ 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=34.978..34.978 rows=116,994 loops=1)

  • Index Cond: (classification = 2)
34. 0.015 0.015 ↑ 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.42 rows=1 width=58) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (order_item_id = item_options.id)
  • Filter: (NOT del_flg)
35. 1.750 1.750 ↑ 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.15 rows=1 width=58) (actual time=0.007..0.007 rows=1 loops=250)

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

Hash (cost=13.80..13.80 rows=190 width=183) (actual time=0.020..0.020 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.017 0.017 ↑ 190.0 1 1

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

  • Filter: (NOT del_flg)
38. 5.162 12.772 ↑ 1.0 4,860 1

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

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

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

  • Hash Cond: (discount_service_charges.order_discount_id = discounts.id)
40. 0.006 0.006 ↓ 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.006..0.006 rows=0 loops=1)

  • Filter: (NOT del_flg)
41. 1.230 3.565 ↑ 1.0 4,860 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 702kB
42. 2.335 2.335 ↑ 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.012..2.335 rows=4,860 loops=1)

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

Hash (cost=13.80..13.80 rows=190 width=183) (actual time=0.033..0.033 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 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=183) (actual time=0.028..0.029 rows=1 loops=1)

  • Filter: (NOT del_flg)
45. 0.000 0.006 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
46. 0.006 0.006 ↓ 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.006..0.006 rows=0 loops=1)

  • Filter: (NOT del_flg)
47. 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
48. 0.003 0.003 ↓ 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.003..0.003 rows=0 loops=1)

  • Filter: (NOT del_flg)
49. 0.496 1.309 ↑ 1.0 1,750 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 200kB
50. 0.813 0.813 ↑ 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.012..0.813 rows=1,750 loops=1)

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

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

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

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

  • Buckets: 32768 Batches: 4 Memory Usage: 2075kB
53. 36.751 36.751 ↑ 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.028..36.751 rows=71,504 loops=1)

  • Filter: (NOT del_flg)
54. 0.001 0.011 ↓ 0.0 0 1

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

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

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

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

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

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

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

58. 0.000 0.005 ↓ 0.0 0 1

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

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

  • Filter: ((NOT del_flg) AND (bill_id IS NULL))
Planning time : 20.395 ms
Execution time : 709.976 ms