explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qtn

Settings
# exclusive inclusive rows x rows loops node
1. 4.548 1,569.336 ↑ 12.6 250 1

Sort (cost=103,167.99..103,175.90 rows=3,162 width=4,428) (actual time=1,569.288..1,569.336 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.758 1,564.788 ↑ 12.6 250 1

Hash Left Join (cost=63,099.68..96,964.17 rows=3,162 width=4,428) (actual time=1,501.506..1,564.788 rows=250 loops=1)

  • Hash Cond: (orders.id = epos_cat_authorize_sales.order_id)
3. 2.332 1,564.025 ↑ 12.6 250 1

Hash Left Join (cost=63,088.97..96,941.59 rows=3,162 width=3,307) (actual time=1,501.440..1,564.025 rows=250 loops=1)

  • Hash Cond: (orders.id = order_customers.order_id)
4. 0.721 1,561.679 ↑ 12.6 250 1

Hash Left Join (cost=63,060.97..96,589.39 rows=3,162 width=3,233) (actual time=1,499.818..1,561.679 rows=250 loops=1)

  • Hash Cond: (orders.id = servicecharges.order_id)
5. 57.996 1,560.948 ↑ 12.6 250 1

Hash Left Join (cost=63,046.74..96,515.86 rows=3,162 width=2,950) (actual time=1,499.759..1,560.948 rows=250 loops=1)

  • Hash Cond: (orders.id = taxes.order_id)
6. 32.040 1,306.238 ↑ 12.6 250 1

Hash Right Join (cost=59,785.90..90,217.36 rows=3,162 width=2,887) (actual time=1,302.977..1,306.238 rows=250 loops=1)

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

  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 5,858
8. 5.314 1,006.846 ↑ 12.6 250 1

Hash (cost=58,748.38..58,748.38 rows=3,162 width=2,556) (actual time=1,006.846..1,006.846 rows=250 loops=1)

  • Buckets: 1,024 Batches: 4 Memory Usage: 56kB
9. 0.642 1,001.532 ↑ 12.6 250 1

Nested Loop Left Join (cost=23,482.96..58,748.38 rows=3,162 width=2,556) (actual time=994.163..1,001.532 rows=250 loops=1)

10. 0.543 999.390 ↑ 12.6 250 1

Hash Left Join (cost=23,482.54..46,476.93 rows=3,162 width=2,437) (actual time=994.132..999.390 rows=250 loops=1)

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

Hash Left Join (cost=23,419.16..46,391.17 rows=3,162 width=2,368) (actual time=985.295..990.066 rows=250 loops=1)

  • Hash Cond: (item_options.id = option_service_charges.order_item_id)
12. 0.472 989.551 ↑ 12.6 250 1

Hash Left Join (cost=23,403.31..46,296.26 rows=3,162 width=2,171) (actual time=985.243..989.551 rows=250 loops=1)

  • Hash Cond: (item_options.id = option_discounts.order_item_id)
13. 0.640 989.035 ↑ 12.6 250 1

Hash Left Join (cost=23,387.14..46,197.07 rows=3,162 width=1,988) (actual time=985.132..989.035 rows=250 loops=1)

  • Hash Cond: (orders.id = discounts.order_id)
14. 0.354 951.794 ↑ 12.6 250 1

Merge Left Join (cost=23,109.50..45,772.59 rows=3,162 width=1,750) (actual time=948.393..951.794 rows=250 loops=1)

  • Merge Cond: (order_items.id = item_service_charges.order_item_id)
15. 0.395 951.423 ↑ 12.6 250 1

Merge Left Join (cost=23,089.15..45,743.43 rows=3,162 width=1,553) (actual time=948.358..951.423 rows=250 loops=1)

  • Merge Cond: (order_items.id = item_discounts.order_item_id)
16. 0.440 950.957 ↑ 12.6 250 1

Nested Loop Left Join (cost=23,068.16..45,713.58 rows=3,162 width=1,370) (actual time=948.279..950.957 rows=250 loops=1)

17. 0.598 947.267 ↑ 12.6 250 1

Merge Left Join (cost=23,067.73..23,083.57 rows=3,162 width=1,312) (actual time=946.569..947.267 rows=250 loops=1)

  • Merge Cond: (order_items.id = item_options.parent_id)
18. 2.792 863.591 ↑ 12.6 250 1

Sort (cost=18,947.51..18,955.41 rows=3,162 width=821) (actual time=863.483..863.591 rows=250 loops=1)

  • Sort Key: order_items.id
  • Sort Method: quicksort Memory: 121kB
19. 0.503 860.799 ↑ 12.6 250 1

Hash Right Join (cost=8,085.69..18,763.69 rows=3,162 width=821) (actual time=859.943..860.799 rows=250 loops=1)

  • Hash Cond: (order_items.order_id = orders.id)
20. 607.217 675.913 ↑ 13.9 385 1

Hash Right Join (cost=6,461.82..17,119.13 rows=5,342 width=482) (actual time=189.176..675.913 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=392,822 width=49) (actual time=0.063..506.653 rows=392,935 loops= (...)
  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 1,267
21. 68.696 68.696 ↑ 14.3 373 1

Hash (cost=6,395.04..6,395.04 rows=5,342 width=433) (actual time=68.696..68.696 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=0.092. (...)
  • Index Cond: (bill_id IS NULL)
  • Filter: ((NOT del_flg) AND (classification = 1))
  • Rows Removed by Filter: 4,912
22. 0.050 184.383 ↑ 105.4 30 1

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

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

Hash Right Join (cost=4.46..1,584.34 rows=3,162 width=339) (actual time=184.251..184.333 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=181.977..182 (...)
  • Index Cond: (payment_complete = false)
  • Filter: ((NOT del_flg) AND (NOT payment_complete) AND (shop_id = 1))
  • Rows Removed by Filter: 5,858
24. 2.086 2.229 ↑ 1.0 30 1

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

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

Seq Scan on pos_table t (cost=0.00..3.66 rows=30 width=8) (actual time=0.061..0.143 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.042 83.078 ↑ 155.0 1 1

Sort (cost=4,120.22..4,120.61 rows=155 width=491) (actual time=83.077..83.078 rows=1 loops=1)

  • Sort Key: item_options.parent_id
  • Sort Method: quicksort Memory: 25kB
27. 0.056 83.036 ↑ 155.0 1 1

Nested Loop Left Join (cost=2,205.63..4,114.59 rows=155 width=491) (actual time=83.034..83.036 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=0.030..0.0 (...)
28. 1.004 82.980 ↑ 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=82.979..82.980 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. 80.708 81.976 ↓ 0.0 0 1

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

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

  • Index Cond: (bill_id IS NULL)
  • Index Cond: (classification = 2)
31. 3.250 3.250 ↑ 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.013..0.013 rows=1 loops=250)

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

Sort (cost=20.99..21.47 rows=190 width=183) (actual time=0.071..0.071 rows=1 loops=1)

  • Sort Key: item_discounts.order_item_id
  • Sort Method: quicksort Memory: 25kB
33. 0.047 0.047 ↑ 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.045..0.047 rows=1 loops=1)

  • Filter: (NOT del_flg)
34. 0.008 0.017 ↓ 0.0 0 1

Sort (cost=20.34..20.79 rows=180 width=197) (actual time=0.017..0.017 rows=0 loops=1)

  • Sort Key: item_service_charges.order_item_id
  • Sort Method: quicksort Memory: 25kB
35. 0.009 0.009 ↓ 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.009..0.009 rows=0 loops=1)

  • Filter: (NOT del_flg)
36. 22.544 36.601 ↑ 1.0 4,860 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 705kB
37. 5.386 14.057 ↑ 1.0 4,860 1

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

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

  • Filter: (NOT del_flg)
39. 3.065 8.660 ↑ 1.0 4,860 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 702kB
40. 5.595 5.595 ↑ 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.032..5.595 rows=4,860 loops=1)

  • Filter: (NOT del_flg)
  • Rows Removed by Filter: 395
41. 0.014 0.044 ↑ 190.0 1 1

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

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

  • Filter: (NOT del_flg)
43. 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: 1,024 Batches: 1 Memory Usage: 8kB
44. 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)
45. 7.304 8.781 ↑ 1.0 1,750 1

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

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

  • Filter: (NOT del_flg)
47. 1.500 1.500 ↑ 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.005..0.006 rows=1 loops=250)

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

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

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

  • Filter: (NOT del_flg)
50. 0.000 0.010 ↓ 0.0 0 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
51. 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)
52. 0.001 0.014 ↓ 0.0 0 1

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

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

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

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

  • Filter: ((NOT del_flg) AND (bill_id IS NULL))
Planning time : 124.346 ms
Execution time : 1,583.362 ms