explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7PDp

Settings
# exclusive inclusive rows x rows loops node
1. 98.244 971.227 ↓ 1.6 5,114 1

Sort (cost=101,941.78..101,949.87 rows=3,237 width=3,996) (actual time=969.732..971.227 rows=5,114 loops=1)

  • Sort Key: orders.table_branch, order_items.order_count, order_items.id, item_variations.id
  • Sort Method: quicksort Memory: 2054kB
2. 12.592 872.983 ↓ 1.6 5,114 1

Hash Left Join (cost=63,721.99..96,184.56 rows=3,237 width=3,996) (actual time=742.291..872.983 rows=5,114 loops=1)

  • Hash Cond: (orders.id = epos_cat_authorize_sales.order_id)
3. 11.841 860.386 ↓ 1.6 5,114 1

Hash Left Join (cost=63,711.28..96,161.69 rows=3,237 width=2,907) (actual time=742.261..860.386 rows=5,114 loops=1)

  • Hash Cond: (orders.id = order_customers.order_id)
4. 11.844 848.540 ↓ 1.6 5,114 1

Hash Left Join (cost=63,683.28..95,801.80 rows=3,237 width=2,857) (actual time=742.223..848.540 rows=5,114 loops=1)

  • Hash Cond: (orders.id = servicecharges.order_id)
5. 68.647 836.688 ↓ 1.6 5,114 1

Hash Left Join (cost=63,667.43..95,672.63 rows=3,237 width=2,598) (actual time=742.185..836.688 rows=5,114 loops=1)

  • Hash Cond: (orders.id = taxes.order_id)
6. 54.509 665.017 ↓ 1.6 5,114 1

Hash Right Join (cost=60,616.59..89,997.64 rows=3,237 width=2,559) (actual time=638.800..665.017 rows=5,114 loops=1)

  • Hash Cond: (child_orders.parent_id = orders.id)
7. 149.591 149.591 ↑ 1.0 256,803 1

Seq Scan on pos_order child_orders (cost=0.00..6,451.03 rows=256,803 width=307) (actual time=0.016..149.591 rows=256,803 loops=1)

8. 22.315 460.917 ↓ 1.6 5,114 1

Hash (cost=59,675.12..59,675.12 rows=3,237 width=2,252) (actual time=460.917..460.917 rows=5,114 loops=1)

  • Buckets: 2048 Batches: 4 Memory Usage: 342kB
9. 15.663 438.602 ↓ 1.6 5,114 1

Nested Loop Left Join (cost=23,530.88..59,675.12 rows=3,237 width=2,252) (actual time=300.763..438.602 rows=5,114 loops=1)

10. 9.406 387.141 ↓ 1.6 5,114 1

Hash Left Join (cost=23,530.46..47,217.66 rows=3,237 width=2,157) (actual time=300.737..387.141 rows=5,114 loops=1)

  • Hash Cond: (discounts.id = discount_taxes.order_discount_id)
11. 8.652 375.793 ↓ 1.6 5,114 1

Hash Left Join (cost=23,467.08..47,131.37 rows=3,237 width=2,120) (actual time=298.785..375.793 rows=5,114 loops=1)

  • Hash Cond: (item_options.id = option_service_charges.order_item_id)
12. 8.217 367.136 ↓ 1.6 5,114 1

Hash Left Join (cost=23,448.98..46,959.50 rows=3,237 width=1,955) (actual time=298.772..367.136 rows=5,114 loops=1)

  • Hash Cond: (item_options.id = option_discounts.order_item_id)
13. 9.047 358.890 ↓ 1.6 5,114 1

Hash Left Join (cost=23,430.43..46,779.09 rows=3,237 width=1,796) (actual time=298.726..358.890 rows=5,114 loops=1)

  • Hash Cond: (orders.id = discounts.order_id)
14. 6.670 331.495 ↓ 1.6 5,112 1

Merge Left Join (cost=23,139.58..46,337.87 rows=3,237 width=1,598) (actual time=280.360..331.495 rows=5,112 loops=1)

  • Merge Cond: (order_items.id = item_service_charges.order_item_id)
15. 7.253 324.817 ↓ 1.6 5,112 1

Merge Left Join (cost=23,110.70..46,299.08 rows=3,237 width=1,425) (actual time=280.348..324.817 rows=5,112 loops=1)

  • Merge Cond: (order_items.id = item_discounts.order_item_id)
16. 25.020 317.534 ↓ 1.6 5,112 1

Nested Loop Left Join (cost=23,080.62..46,258.99 rows=3,237 width=1,266) (actual time=280.314..317.534 rows=5,112 loops=1)

  • -> 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.003..0.003 rows=0 loops=5112 (...)
17. 10.150 292.514 ↓ 1.6 5,112 1

Merge Left Join (cost=23,080.19..23,096.40 rows=3,237 width=1,232) (actual time=280.304..292.514 rows=5,112 loops=1)

  • Merge Cond: (order_items.id = item_options.parent_id)
  • Index Cond: (order_item_id = order_items.id)
18. 11.486 242.196 ↓ 1.6 5,084 1

Sort (cost=18,959.96..18,968.05 rows=3,237 width=781) (actual time=240.188..242.196 rows=5,084 loops=1)

  • Sort Key: order_items.id
  • Sort Method: quicksort Memory: 1116kB
19. 6.939 230.710 ↓ 1.6 5,084 1

Hash Right Join (cost=8,087.99..18,771.24 rows=3,237 width=781) (actual time=33.653..230.710 rows=5,084 loops=1)

  • Hash Cond: (order_items.order_id = orders.id)
20. 193.699 209.624 ↓ 1.1 5,897 1

Hash Right Join (cost=6,461.91..17,124.42 rows=5,349 width=466) (actual time=16.733..209.624 rows=5,897 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=394202 width=49) (actual time=0.020..72.030 rows=394202 loops=1 (...)
21. 15.925 15.925 ↑ 1.0 5,154 1

Hash (cost=6,395.04..6,395.04 rows=5,349 width=417) (actual time=15.925..15.925 rows=5,154 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 876kB
  • -> Index Scan using pos_order_item_bill_id_indx on pos_order_item order_items (cost=0.43..6395.04 rows=5349 width=417) (actual time=0.025. (...)
  • Index Cond: (bill_id IS NULL)
  • Filter: (classification = 1)
  • Rows Removed by Filter: 131
22. 2.616 14.147 ↑ 1.0 3,118 1

Hash (cost=1,585.62..1,585.62 rows=3,237 width=315) (actual time=14.147..14.147 rows=3,118 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 349kB
23. 11.449 11.531 ↑ 1.0 3,118 1

Hash Right Join (cost=4.46..1,585.62 rows=3,237 width=315) (actual time=0.137..11.531 rows=3,118 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=6043 width=315) (actual time=0.044..7.681 (...)
  • Index Cond: (payment_complete = false)
  • Filter: ((NOT payment_complete) AND (shop_id = 1))
24. 0.013 0.082 ↑ 1.0 30 1

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

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

Seq Scan on pos_table t (cost=0.00..3.66 rows=30 width=8) (actual time=0.029..0.069 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.298 40.168 ↑ 1.2 131 1

Sort (cost=4,120.22..4,120.61 rows=155 width=451) (actual time=40.107..40.168 rows=131 loops=1)

  • Sort Key: item_options.parent_id
  • Sort Method: quicksort Memory: 59kB
27. 1.025 39.870 ↑ 1.2 131 1

Nested Loop Left Join (cost=2,205.63..4,114.59 rows=155 width=451) (actual time=38.133..39.870 rows=131 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.005..0.0 (...)
28. 0.819 38.845 ↑ 1.2 131 1

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

  • Recheck Cond: ((bill_id IS NULL) AND (classification = 2))
  • Rows Removed by Index Recheck: 867
  • Heap Blocks: exact=279
  • Index Cond: (order_item_id = item_options.id)
29. 37.116 38.026 ↓ 0.0 0 1

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

  • -> Bitmap Index Scan on pos_order_classification_indx (cost=0.00..2099.16 rows=113431 width=0) (actual time=36.663..36.663 rows=116994 loo (...)
30. 0.910 0.910 ↑ 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.910..0.910 rows=5,285 loops=1)

  • Index Cond: (bill_id IS NULL)
  • Index Cond: (classification = 2)
31. 0.013 0.030 ↑ 380.0 1 1

Sort (cost=30.08..31.03 rows=380 width=159) (actual time=0.030..0.030 rows=1 loops=1)

  • Sort Key: item_discounts.order_item_id
  • Sort Method: quicksort Memory: 25kB
32. 0.017 0.017 ↑ 380.0 1 1

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

33. 0.003 0.008 ↓ 0.0 0 1

Sort (cost=28.89..29.79 rows=360 width=173) (actual time=0.008..0.008 rows=0 loops=1)

  • Sort Key: item_service_charges.order_item_id
  • Sort Method: quicksort Memory: 25kB
34. 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=360 width=173) (actual time=0.005..0.005 rows=0 loops=1)

35. 4.891 18.348 ↑ 1.0 5,255 1

Hash (cost=225.16..225.16 rows=5,255 width=198) (actual time=18.348..18.348 rows=5,255 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 683kB
36. 4.070 13.457 ↑ 1.0 5,255 1

Hash Right Join (cost=204.24..225.16 rows=5,255 width=198) (actual time=9.399..13.457 rows=5,255 loops=1)

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

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

38. 5.146 9.378 ↑ 1.0 5,255 1

Hash (cost=138.55..138.55 rows=5,255 width=75) (actual time=9.378..9.378 rows=5,255 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 681kB
39. 4.232 4.232 ↑ 1.0 5,255 1

Seq Scan on pos_order_discount discounts (cost=0.00..138.55 rows=5,255 width=75) (actual time=0.012..4.232 rows=5,255 loops=1)

40. 0.003 0.029 ↑ 380.0 1 1

Hash (cost=13.80..13.80 rows=380 width=159) (actual time=0.029..0.029 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.026 0.026 ↑ 380.0 1 1

Seq Scan on pos_order_item_discount option_discounts (cost=0.00..13.80 rows=380 width=159) (actual time=0.025..0.026 rows=1 loops=1)

42. 0.000 0.005 ↓ 0.0 0 1

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

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

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

44. 1.051 1.942 ↑ 1.0 1,750 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 156kB
45. 0.891 0.891 ↑ 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.011..0.891 rows=1,750 loops=1)

46. 35.798 35.798 ↑ 1.0 1 5,114

Index Scan using pos_customer_attribute_order_id_indx on pos_customer_attribute customer_attribute (cost=0.42..3.84 rows=1 width=95) (actual time=0.006..0.007 rows=1 loops=5,114)

  • Index Cond: (order_id = orders.id)
47. 63.878 103.024 ↑ 1.0 71,504 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 1798kB
48. 39.146 39.146 ↑ 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.020..39.146 rows=71,504 loops=1)

49. 0.000 0.008 ↓ 0.0 0 1

Hash (cost=12.60..12.60 rows=260 width=259) (actual time=0.008..0.008 rows=0 loops=1)

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

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

51. 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
52. 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)

53. 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
54. 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: (bill_id IS NULL)
Planning time : 17.811 ms
Execution time : 990.126 ms