explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9lOb

Settings
# exclusive inclusive rows x rows loops node
1. 0.161 12,401.576 ↑ 1.0 18 1

Sort (cost=747,184.97..747,185.01 rows=18 width=794) (actual time=12,401.574..12,401.576 rows=18 loops=1)

  • Sort Key: (max(p.product_group_id)), (max((p.name)::text))
  • Sort Method: quicksort Memory: 104kB
2. 227.738 12,401.415 ↑ 1.0 18 1

GroupAggregate (cost=17,775.78..747,184.59 rows=18 width=794) (actual time=84.399..12,401.415 rows=18 loops=1)

  • Group Key: p.id
3. 41.523 102.543 ↓ 10.7 17,170 1

Sort (cost=17,775.78..17,779.81 rows=1,612 width=923) (actual time=83.845..102.543 rows=17,170 loops=1)

  • Sort Key: p.id
  • Sort Method: external merge Disk: 12,976kB
4. 12.559 61.020 ↓ 10.7 17,170 1

Hash Right Join (cost=2,094.82..17,689.90 rows=1,612 width=923) (actual time=45.462..61.020 rows=17,170 loops=1)

  • Hash Cond: (oi.item_type_id = p.id)
5. 39.900 44.961 ↓ 1.0 84,900 1

Bitmap Heap Scan on order_item_t oi (cost=1,630.99..16,365.92 rows=84,403 width=4) (actual time=6.789..44.961 rows=84,900 loops=1)

  • Recheck Cond: ((item_type)::text = 'M'::text)
  • Filter: (cancelled = 0)
  • Rows Removed by Filter: 1,793
  • Heap Blocks: exact=12,776
6. 5.061 5.061 ↓ 1.0 87,960 1

Bitmap Index Scan on oi_item_type_i (cost=0.00..1,609.89 rows=86,062 width=0) (actual time=5.061..5.061 rows=87,960 loops=1)

  • Index Cond: ((item_type)::text = 'M'::text)
7. 0.188 3.500 ↓ 2.1 219 1

Hash (cost=462.50..462.50 rows=106 width=923) (actual time=3.500..3.500 rows=219 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 176kB
8. 0.103 3.312 ↓ 2.1 219 1

Hash Left Join (cost=410.36..462.50 rows=106 width=923) (actual time=3.041..3.312 rows=219 loops=1)

  • Hash Cond: (p.image_file_id = f.id)
9. 0.056 1.605 ↓ 2.1 219 1

Hash Left Join (cost=215.30..267.17 rows=106 width=890) (actual time=1.403..1.605 rows=219 loops=1)

  • Hash Cond: (ap.quick_action_id = a.id)
10. 0.062 1.537 ↓ 2.1 219 1

Hash Left Join (cost=204.85..256.43 rows=106 width=744) (actual time=1.385..1.537 rows=219 loops=1)

  • Hash Cond: (p.product_limit_id = pl.id)
11. 0.112 1.137 ↓ 2.1 219 1

Hash Right Join (cost=152.36..203.66 rows=106 width=736) (actual time=1.036..1.137 rows=219 loops=1)

  • Hash Cond: (ap.product_id = p.id)
12. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on quick_action_product_t ap (cost=0.00..30.40 rows=2,040 width=8) (actual time=0.000..0.000 rows=0 loops=1)

13. 0.188 1.025 ↓ 2.1 219 1

Hash (cost=151.03..151.03 rows=106 width=732) (actual time=1.025..1.025 rows=219 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 169kB
14. 0.053 0.837 ↓ 2.1 219 1

Nested Loop Left Join (cost=75.14..151.03 rows=106 width=732) (actual time=0.617..0.837 rows=219 loops=1)

15. 0.013 0.724 ↓ 1.1 20 1

Nested Loop Left Join (cost=74.85..138.34 rows=18 width=706) (actual time=0.607..0.724 rows=20 loops=1)

16. 0.010 0.711 ↓ 1.1 20 1

Hash Left Join (cost=74.71..135.04 rows=18 width=560) (actual time=0.603..0.711 rows=20 loops=1)

  • Hash Cond: (p.product_group_id = pg.id)
17. 0.005 0.472 ↓ 1.1 20 1

Nested Loop (cost=48.10..108.38 rows=18 width=542) (actual time=0.368..0.472 rows=20 loops=1)

18. 0.001 0.017 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.43..16.52 rows=1 width=20) (actual time=0.017..0.017 rows=1 loops=1)

19. 0.010 0.010 ↑ 1.0 1 1

Index Scan using event_instance_t_pkey on event_instance_t ei (cost=0.28..8.29 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (id = 813)
20. 0.006 0.006 ↑ 1.0 1 1

Index Scan using currency_t_pkey on currency_t c (cost=0.15..8.17 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (id = ei.currency_id)
21. 0.190 0.450 ↓ 1.1 20 1

Hash Right Join (cost=47.68..91.68 rows=18 width=526) (actual time=0.350..0.450 rows=20 loops=1)

  • Hash Cond: (po.product_id = p.id)
22. 0.169 0.169 ↓ 1.0 2,108 1

Seq Scan on product_option_t po (cost=0.00..36.03 rows=2,103 width=20) (actual time=0.003..0.169 rows=2,108 loops=1)

23. 0.014 0.091 ↑ 1.0 18 1

Hash (cost=47.45..47.45 rows=18 width=510) (actual time=0.091..0.091 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
24. 0.020 0.077 ↑ 1.0 18 1

Hash Right Join (cost=11.70..47.45 rows=18 width=510) (actual time=0.069..0.077 rows=18 loops=1)

  • Hash Cond: (ptp.product_id = p.id)
25. 0.002 0.002 ↑ 510.0 4 1

Seq Scan on product_tag_product_t ptp (cost=0.00..30.40 rows=2,040 width=8) (actual time=0.002..0.002 rows=4 loops=1)

26. 0.020 0.055 ↑ 1.0 18 1

Hash (cost=11.47..11.47 rows=18 width=506) (actual time=0.055..0.055 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
27. 0.035 0.035 ↑ 1.0 18 1

Index Scan using pr_instance_id_i on product_t p (cost=0.28..11.47 rows=18 width=506) (actual time=0.022..0.035 rows=18 loops=1)

  • Index Cond: (instance_id = 813)
28. 0.133 0.229 ↑ 1.0 827 1

Hash (cost=16.27..16.27 rows=827 width=18) (actual time=0.229..0.229 rows=827 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 50kB
29. 0.096 0.096 ↑ 1.0 827 1

Seq Scan on product_group_t pg (cost=0.00..16.27 rows=827 width=18) (actual time=0.003..0.096 rows=827 loops=1)

30. 0.000 0.000 ↓ 0.0 0 20

Index Scan using product_tag_t_pkey on product_tag_t pt (cost=0.14..0.18 rows=1 width=150) (actual time=0.000..0.000 rows=0 loops=20)

  • Index Cond: (ptp.product_tag_id = id)
31. 0.060 0.060 ↓ 1.7 10 20

Index Scan using poi_product_option_id_i on product_option_item_t poi (cost=0.29..0.64 rows=6 width=30) (actual time=0.002..0.003 rows=10 loops=20)

  • Index Cond: (product_option_id = po.id)
32. 0.196 0.338 ↓ 1.0 1,941 1

Hash (cost=28.33..28.33 rows=1,933 width=8) (actual time=0.338..0.338 rows=1,941 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 92kB
33. 0.142 0.142 ↓ 1.0 1,941 1

Seq Scan on product_limit_t pl (cost=0.00..28.33 rows=1,933 width=8) (actual time=0.006..0.142 rows=1,941 loops=1)

34. 0.005 0.012 ↓ 1.1 22 1

Hash (cost=10.20..10.20 rows=20 width=150) (actual time=0.012..0.012 rows=22 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
35. 0.007 0.007 ↓ 1.1 22 1

Seq Scan on quick_action_t a (cost=0.00..10.20 rows=20 width=150) (actual time=0.004..0.007 rows=22 loops=1)

36. 0.813 1.604 ↓ 1.0 5,349 1

Hash (cost=128.36..128.36 rows=5,336 width=37) (actual time=1.604..1.604 rows=5,349 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 441kB
37. 0.791 0.791 ↓ 1.0 5,349 1

Seq Scan on file_t f (cost=0.00..128.36 rows=5,336 width=37) (actual time=0.003..0.791 rows=5,349 loops=1)

38.          

SubPlan (for GroupAggregate)

39. 0.009 0.624 ↑ 1.0 1 3

Aggregate (cost=521.15..521.17 rows=1 width=32) (actual time=0.208..0.208 rows=1 loops=3)

40. 0.006 0.615 ↑ 2.0 1 3

Unique (cost=521.10..521.12 rows=2 width=16) (actual time=0.204..0.205 rows=1 loops=3)

41. 0.012 0.609 ↑ 2.0 1 3

Sort (cost=521.10..521.10 rows=2 width=16) (actual time=0.203..0.203 rows=1 loops=3)

  • Sort Key: (COALESCE(sum((oi_1.qty - oi_1.adjust_qty)), '0'::bigint)), p_1.product_limit_id, (max(pl_1.max_available))
  • Sort Method: quicksort Memory: 25kB
42. 0.006 0.597 ↑ 2.0 1 3

Append (cost=0.99..521.09 rows=2 width=16) (actual time=0.179..0.199 rows=1 loops=3)

43. 0.042 0.534 ↑ 1.0 1 3

GroupAggregate (cost=0.99..254.12 rows=1 width=16) (actual time=0.178..0.178 rows=1 loops=3)

  • Group Key: p_1.product_limit_id
44. 0.030 0.492 ↓ 4.1 61 3

Nested Loop (cost=0.99..253.96 rows=15 width=16) (actual time=0.048..0.164 rows=61 loops=3)

45. 0.012 0.105 ↑ 1.0 1 3

Nested Loop (cost=0.56..19.87 rows=1 width=12) (actual time=0.028..0.035 rows=1 loops=3)

46. 0.024 0.024 ↑ 1.0 1 3

Index Scan using product_limit_t_pkey on product_limit_t pl_1 (cost=0.28..8.29 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=3)

  • Index Cond: (id = p.product_limit_id)
47. 0.069 0.069 ↑ 1.0 1 3

Index Scan using pr_instance_id_i on product_t p_1 (cost=0.28..11.56 rows=1 width=8) (actual time=0.016..0.023 rows=1 loops=3)

  • Index Cond: (instance_id = 813)
  • Filter: ((product_limit_id IS NOT NULL) AND (product_limit_id <> 0) AND (product_limit_id = p.product_limit_id))
  • Rows Removed by Filter: 17
48. 0.357 0.357 ↓ 4.1 61 3

Index Scan using oi_item_type_id_i on order_item_t oi_1 (cost=0.42..233.95 rows=15 width=12) (actual time=0.017..0.119 rows=61 loops=3)

  • Index Cond: (item_type_id = p_1.id)
  • Filter: (((item_type)::text = 'M'::text) AND (cancelled = 0))
  • Rows Removed by Filter: 10
49. 0.003 0.057 ↓ 0.0 0 3

GroupAggregate (cost=14.34..266.95 rows=1 width=16) (actual time=0.019..0.019 rows=0 loops=3)

  • Group Key: pl_2.id
50. 0.000 0.054 ↓ 0.0 0 3

Nested Loop (cost=14.34..266.93 rows=1 width=16) (actual time=0.018..0.018 rows=0 loops=3)

51. 0.003 0.054 ↓ 0.0 0 3

Nested Loop (cost=14.05..265.91 rows=1 width=20) (actual time=0.018..0.018 rows=0 loops=3)

52. 0.003 0.051 ↓ 0.0 0 3

Nested Loop Left Join (cost=13.77..257.60 rows=1 width=20) (actual time=0.017..0.017 rows=0 loops=3)

53. 0.003 0.048 ↓ 0.0 0 3

Nested Loop (cost=13.35..253.88 rows=1 width=16) (actual time=0.016..0.016 rows=0 loops=3)

54. 0.018 0.045 ↓ 0.0 0 3

Hash Right Join (cost=13.07..245.58 rows=1 width=12) (actual time=0.015..0.015 rows=0 loops=3)

  • Hash Cond: (oio.product_option_item_id = poi_1.id)
55. 0.000 0.000 ↓ 0.0 0

Seq Scan on order_item_option_t oio (cost=0.00..198.71 rows=12,871 width=8) (never executed)

56. 0.006 0.027 ↓ 0.0 0 3

Hash (cost=13.06..13.06 rows=1 width=12) (actual time=0.009..0.009 rows=0 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
57. 0.021 0.021 ↓ 0.0 0 3

Index Scan using poi_product_limit_id_i on product_option_item_t poi_1 (cost=0.29..13.06 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=3)

  • Index Cond: ((product_limit_id IS NOT NULL) AND (product_limit_id = p.product_limit_id))
  • Filter: (product_limit_id <> 0)
58. 0.000 0.000 ↓ 0.0 0

Index Scan using product_limit_t_pkey on product_limit_t pl_2 (cost=0.28..8.29 rows=1 width=8) (never executed)

  • Index Cond: (id = p.product_limit_id)
59. 0.000 0.000 ↓ 0.0 0

Index Scan using order_item_t_pkey on order_item_t oi_2 (cost=0.42..3.72 rows=1 width=12) (never executed)

  • Index Cond: (id = oio.order_item_id)
  • Filter: (((item_type)::text = 'M'::text) AND (cancelled = 0))
60. 0.000 0.000 ↓ 0.0 0

Index Scan using product_option_t_pkey on product_option_t po_1 (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (id = poi_1.product_option_id)
61. 0.000 0.000 ↓ 0.0 0

Index Scan using product_t_pkey on product_t p_2 (cost=0.28..0.95 rows=1 width=4) (never executed)

  • Index Cond: (id = po_1.product_id)
  • Filter: (instance_id = 813)
62. 17.170 12,070.510 ↑ 1.0 1 17,170

Aggregate (cost=446.57..446.59 rows=1 width=32) (actual time=0.703..0.703 rows=1 loops=17,170)

63. 0.000 12,053.340 ↓ 0.0 0 17,170

Unique (cost=446.52..446.54 rows=2 width=16) (actual time=0.702..0.702 rows=0 loops=17,170)

64. 34.340 12,053.340 ↓ 0.0 0 17,170

Sort (cost=446.52..446.53 rows=2 width=16) (actual time=0.702..0.702 rows=0 loops=17,170)

  • Sort Key: (COALESCE(sum((oi_3.qty - oi_3.adjust_qty)), '0'::bigint)), p_3.product_limit_id, (max(pl_3.max_available))
  • Sort Method: quicksort Memory: 25kB
65. 17.170 12,019.000 ↓ 0.0 0 17,170

Append (cost=0.99..446.51 rows=2 width=16) (actual time=0.700..0.700 rows=0 loops=17,170)

66. 0.000 34.340 ↓ 0.0 0 17,170

GroupAggregate (cost=0.99..254.12 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=17,170)

  • Group Key: p_3.product_limit_id
67. 0.000 34.340 ↓ 0.0 0 17,170

Nested Loop (cost=0.99..253.96 rows=15 width=16) (actual time=0.002..0.002 rows=0 loops=17,170)

68. 17.170 34.340 ↓ 0.0 0 17,170

Nested Loop (cost=0.56..19.87 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=17,170)

69. 17.170 17.170 ↓ 0.0 0 17,170

Index Scan using product_limit_t_pkey on product_limit_t pl_3 (cost=0.28..8.29 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=17,170)

  • Index Cond: (id = poi.product_limit_id)
70. 0.000 0.000 ↓ 0.0 0

Index Scan using pr_instance_id_i on product_t p_3 (cost=0.28..11.56 rows=1 width=8) (never executed)

  • Index Cond: (instance_id = 813)
  • Filter: ((product_limit_id IS NOT NULL) AND (product_limit_id <> 0) AND (product_limit_id = poi.product_limit_id))
71. 0.000 0.000 ↓ 0.0 0

Index Scan using oi_item_type_id_i on order_item_t oi_3 (cost=0.42..233.95 rows=15 width=12) (never executed)

  • Index Cond: (item_type_id = p_3.id)
  • Filter: (((item_type)::text = 'M'::text) AND (cancelled = 0))
72. 0.000 11,967.490 ↓ 0.0 0 17,170

GroupAggregate (cost=12.97..192.37 rows=1 width=16) (actual time=0.697..0.697 rows=0 loops=17,170)

  • Group Key: pl_4.id
73. 0.000 11,967.490 ↓ 0.0 0 17,170

Nested Loop Left Join (cost=12.97..192.35 rows=1 width=16) (actual time=0.697..0.697 rows=0 loops=17,170)

74. 0.000 11,967.490 ↓ 0.0 0 17,170

Nested Loop Left Join (cost=12.54..188.63 rows=1 width=12) (actual time=0.697..0.697 rows=0 loops=17,170)

75. 516.134 11,967.490 ↓ 0.0 0 17,170

Nested Loop (cost=12.26..67.58 rows=1 width=12) (actual time=0.697..0.697 rows=0 loops=17,170)

76. 377.740 8,052.730 ↓ 198.0 198 17,170

Nested Loop (cost=11.98..59.27 rows=1 width=8) (actual time=0.280..0.469 rows=198 loops=17,170)

77. 3,743.032 6,558.940 ↑ 1.8 5 17,170

Hash Join (cost=11.70..53.25 rows=9 width=4) (actual time=0.274..0.382 rows=5 loops=17,170)

  • Hash Cond: (po_2.product_id = p_4.id)
78. 2,815.880 2,815.880 ↓ 1.0 2,108 17,170

Seq Scan on product_option_t po_2 (cost=0.00..36.03 rows=2,103 width=8) (actual time=0.003..0.164 rows=2,108 loops=17,170)

79. 0.003 0.028 ↑ 1.0 18 1

Hash (cost=11.47..11.47 rows=18 width=4) (actual time=0.028..0.028 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
80. 0.025 0.025 ↑ 1.0 18 1

Index Scan using pr_instance_id_i on product_t p_4 (cost=0.28..11.47 rows=18 width=4) (actual time=0.018..0.025 rows=18 loops=1)

  • Index Cond: (instance_id = 813)
81. 1,116.050 1,116.050 ↓ 40.0 40 85,850

Index Scan using poi_product_option_id_i on product_option_item_t poi_2 (cost=0.29..0.66 rows=1 width=12) (actual time=0.004..0.013 rows=40 loops=85,850)

  • Index Cond: (product_option_id = po_2.id)
  • Filter: (product_limit_id = poi.product_limit_id)
  • Rows Removed by Filter: 1
82. 3,398.626 3,398.626 ↓ 0.0 0 3,398,626

Index Scan using product_limit_t_pkey on product_limit_t pl_4 (cost=0.28..8.29 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=3,398,626)

  • Index Cond: (id = poi.product_limit_id)
83. 0.000 0.000 ↓ 0.0 0

Index Only Scan using oio_covering_i on order_item_option_t oio_1 (cost=0.29..121.00 rows=5 width=8) (never executed)

  • Index Cond: (product_option_item_id = poi_2.id)
  • Heap Fetches: 0
84. 0.000 0.000 ↓ 0.0 0

Index Scan using order_item_t_pkey on order_item_t oi_4 (cost=0.42..3.72 rows=1 width=12) (never executed)

  • Index Cond: (id = oio_1.order_item_id)
  • Filter: (((item_type)::text = 'M'::text) AND (cancelled = 0))
Planning time : 6.894 ms
Execution time : 12,409.784 ms