explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HgPP

Settings
# exclusive inclusive rows x rows loops node
1. 12.964 13,142.612 ↑ 12.6 250 1

Sort (cost=103,770.77..103,778.67 rows=3,162 width=4,444) (actual time=13,142.483..13,142.612 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.936 13,129.648 ↑ 12.6 250 1

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

  • Hash Cond: (orders.id = epos_cat_authorize_sales.order_id)
3. 0.861 13,128.682 ↑ 12.6 250 1

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

  • Hash Cond: (orders.id = order_customers.order_id)
4. 0.924 13,127.804 ↑ 12.6 250 1

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

  • Hash Cond: (orders.id = servicecharges.order_id)
5. 47.153 13,126.839 ↑ 12.6 250 1

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

  • Hash Cond: (orders.id = taxes.order_id)
6. 39.952 12,236.125 ↑ 12.6 250 1

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

  • Hash Cond: (child_orders.parent_id = orders.id)
7. 1,900.042 1,900.042 ↓ 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=8.584..1,900.042 rows=250,945 loops=1)

  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 5,858
8. 73.878 10,296.131 ↑ 12.6 250 1

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

  • Buckets: 1,024 Batches: 4 Memory Usage: 56kB
9. 1.064 10,222.253 ↑ 12.6 250 1

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

10. 0.757 10,213.439 ↑ 12.6 250 1

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

  • Hash Cond: (discounts.id = discount_taxes.order_discount_id)
11. 0.690 9,986.894 ↑ 12.6 250 1

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

  • Hash Cond: (order_items.id = item_service_charges.order_item_id)
12. 0.685 9,986.191 ↑ 12.6 250 1

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

  • Hash Cond: (item_options.id = option_service_charges.order_item_id)
13. 0.647 9,985.464 ↑ 12.6 250 1

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

  • Hash Cond: (item_options.id = option_discounts.order_item_id)
14. 0.681 9,984.747 ↑ 12.6 250 1

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

  • Hash Cond: (orders.id = discounts.order_id)
15. 0.620 9,911.663 ↑ 12.6 250 1

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

  • Hash Cond: (order_items.id = item_discounts.order_item_id)
16. 0.690 9,905.671 ↑ 12.6 250 1

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

17. 1.008 9,884.481 ↑ 12.6 250 1

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

  • Hash Cond: (order_items.id = item_options.parent_id)
18. 1.431 8,309.025 ↑ 12.6 250 1

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

  • Hash Cond: (order_items.order_id = orders.id)
19. 119.463 6,694.319 ↑ 13.9 385 1

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

  • Hash Cond: (item_variations.order_item_id = order_items.id)
20. 4,835.405 4,835.405 ↓ 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=4.588..4,835.405 rows=392,935 loops=1)

  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 1,267
21. 1,739.451 1,739.451 ↑ 14.3 373 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 126kB
  • -> Index Scan using pos_order_item_bill_id_indx on pos_order_item order_items (cost=0.43..6395.04 rows=5,342 width=433) (actual time=9.399..1738. (...)
  • Index Cond: (bill_id IS NULL)
  • Filter: ((NOT del_flg) AND (classification = 1))
  • Rows Removed by Filter: 4,912
22. 0.061 1,613.275 ↑ 105.4 30 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 36kB
23. 1,613.089 1,613.214 ↑ 105.4 30 1

Hash Right Join (cost=4.46..1,584.34 rows=3,162 width=339) (actual time=1,613.156..1,613.214 rows=30 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=5,902 width=331) (actual time=1,613.011..1613.032 (...)
  • Index Cond: (payment_complete = false)
  • Filter: ((NOT del_flg) AND (NOT payment_complete) AND (shop_id = 1))
  • Rows Removed by Filter: 5,858
24. 0.027 0.125 ↑ 1.0 30 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
25. 0.098 0.098 ↑ 1.0 30 1

Seq Scan on pos_table t (cost=0.00..3.66 rows=30 width=8) (actual time=0.043..0.098 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
26. 0.057 1,574.448 ↑ 155.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 2.417 1,574.391 ↑ 155.0 1 1

Nested Loop Left Join (cost=2,205.63..4,114.59 rows=155 width=491) (actual time=1,574.386..1,574.391 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=58) (actual time=1.811..1.8 (...)
28. 7.736 1,571.974 ↑ 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=1,571.973..1,571.974 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)
29. 1,561.456 1,564.238 ↓ 0.0 0 1

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

  • -> Bitmap Index Scan on pos_order_classification_indx (cost=0.00..2099.16 rows=113,431 width=0) (actual time=1,560.754..1560.754 rows=116,994 (...)
30. 2.782 2.782 ↑ 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=2.782..2.782 rows=5,285 loops=1)

  • Index Cond: (bill_id IS NULL)
  • Index Cond: (classification = 2)
31. 20.500 20.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.15 rows=1 width=58) (actual time=0.081..0.082 rows=1 loops=250)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 5.324 5.324 ↑ 190.0 1 1

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

  • Filter: (NOT del_flg)
34. 6.304 72.403 ↑ 1.0 4,860 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 705kB
35. 4.628 66.099 ↑ 1.0 4,860 1

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

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

  • Filter: (NOT del_flg)
37. 2.776 61.441 ↑ 1.0 4,860 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 702kB
38. 58.665 58.665 ↑ 1.0 4,860 1

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
40. 0.038 0.038 ↑ 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.036..0.038 rows=1 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
42. 0.034 0.034 ↓ 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.034..0.034 rows=0 loops=1)

  • Filter: (NOT del_flg)
43. 0.008 0.013 ↓ 0.0 0 1

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

  • Buckets: 1,024 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=197) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: (NOT del_flg)
45. 0.767 225.788 ↑ 1.0 1,750 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 200kB
46. 225.021 225.021 ↑ 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.061..225.021 rows=1,750 loops=1)

  • Filter: (NOT del_flg)
47. 7.750 7.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.030..0.031 rows=1 loops=250)

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

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

  • Buckets: 32,768 Batches: 4 Memory Usage: 2,075kB
49. 781.269 781.269 ↑ 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=2.238..781.269 rows=71,504 loops=1)

  • Filter: (NOT del_flg)
50. 0.016 0.041 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
51. 0.025 0.025 ↓ 0.0 0 1

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
53. 0.015 0.015 ↓ 0.0 0 1

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

54. 0.014 0.030 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
55. 0.016 0.016 ↓ 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.016..0.016 rows=0 loops=1)

  • Filter: ((NOT del_flg) AND (bill_id IS NULL))
Planning time : 526.456 ms
Execution time : 13,154.576 ms