explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RgiI

Settings
# exclusive inclusive rows x rows loops node
1. 7.550 271.391 ↓ 4,037.0 4,037 1

Nested Loop (cost=220,422.27..221,765.35 rows=1 width=133) (actual time=215.135..271.391 rows=4,037 loops=1)

  • Output: mc.machine_id, mc.product_id, (sum(mc.current_quantity)), mc.product_id, (p.price / ('1'::numeric + (t.value / '100'::numeric))), p.blacklisted, (array_agg(s1.avg_sales)), (array_agg(s1.wd)), s1.ms_lvd, s1.ms_lrd
  • Buffers: shared hit=53568
2. 3.646 255.767 ↓ 4,037.0 4,037 1

Nested Loop (cost=220,421.99..221,764.68 rows=1 width=106) (actual time=215.119..255.767 rows=4,037 loops=1)

  • Output: mc.machine_id, mc.product_id, (sum(mc.current_quantity)), p.price, p.blacklisted, p.tax_rate_id, (array_agg(s1.avg_sales)), (array_agg(s1.wd)), s1.ms_lvd, s1.ms_lrd
  • Join Filter: (mc.product_id = p.id)
  • Buffers: shared hit=41457
3. 5.085 240.010 ↓ 16.5 4,037 1

Merge Join (cost=220,421.70..221,514.11 rows=244 width=100) (actual time=215.095..240.010 rows=4,037 loops=1)

  • Output: mc.machine_id, mc.product_id, (sum(mc.current_quantity)), (array_agg(s1.avg_sales)), (array_agg(s1.wd)), s1.ms_lvd, s1.ms_lrd, s1.pid
  • Merge Cond: ((s1.pid = mc.product_id) AND (s1.mid = mc.machine_id))
  • Buffers: shared hit=29346
4. 13.868 214.419 ↑ 1.3 4,148 1

GroupAggregate (cost=210,321.10..211,318.41 rows=5,249 width=88) (actual time=196.320..214.419 rows=4,148 loops=1)

  • Output: s1.mid, s1.pid, array_agg(s1.avg_sales), array_agg(s1.wd), s1.ms_lvd, s1.ms_lrd
  • Group Key: s1.pid, s1.mid, s1.ms_lvd, s1.ms_lrd
  • Buffers: shared hit=26361
5. 12.937 200.551 ↑ 4.0 13,068 1

Sort (cost=210,321.10..210,452.32 rows=52,490 width=88) (actual time=196.298..200.551 rows=13,068 loops=1)

  • Output: s1.mid, s1.pid, s1.ms_lvd, s1.ms_lrd, s1.avg_sales, s1.wd
  • Sort Key: s1.pid, s1.mid, s1.ms_lvd, s1.ms_lrd
  • Sort Method: quicksort Memory: 1405kB
  • Buffers: shared hit=26361
6. 6.129 187.614 ↑ 4.0 13,068 1

Subquery Scan on s1 (cost=202,400.42..206,205.94 rows=52,490 width=88) (actual time=154.675..187.614 rows=13,068 loops=1)

  • Output: s1.mid, s1.pid, s1.ms_lvd, s1.ms_lrd, s1.avg_sales, s1.wd
  • Buffers: shared hit=26361
7. 19.662 181.485 ↑ 4.0 13,068 1

GroupAggregate (cost=202,400.42..205,681.04 rows=52,490 width=88) (actual time=154.673..181.485 rows=13,068 loops=1)

  • Output: ((count(*))::numeric / '4'::numeric), (to_char((date(timezone('Europe/Vienna'::text, vends."timestamp")))::timestamp with time zone, 'ID'::text)), vends.machine_id, vends.product_id, (timezone('Europe/Vienna'::text, ((timezone('Europe/Vienna'::text, machine_statuses.last_vend_timestamp))::date)::timestamp without time zone)), (timezone('Europe/Vienna'::text, ((timezone('Europe/Vienna'::text, machine_statuses.last_refill_timestamp))::date)::timestamp without time zone))
  • Group Key: vends.machine_id, vends.product_id, (to_char((date(timezone('Europe/Vienna'::text, vends."timestamp")))::timestamp with time zone, 'ID'::text)), (timezone('Europe/Vienna'::text, ((timezone('Europe/Vienna'::text, machine_statuses.last_vend_timestamp))::date)::timestamp without time zone)), (timezone('Europe/Vienna'::text, ((timezone('Europe/Vienna'::text, machine_statuses.last_refill_timestamp))::date)::timestamp without time zone))
  • Buffers: shared hit=26361
8. 48.891 161.823 ↑ 2.1 25,267 1

Sort (cost=202,400.42..202,531.64 rows=52,490 width=56) (actual time=154.644..161.823 rows=25,267 loops=1)

  • Output: (to_char((date(timezone('Europe/Vienna'::text, vends."timestamp")))::timestamp with time zone, 'ID'::text)), vends.machine_id, vends.product_id, (timezone('Europe/Vienna'::text, ((timezone('Europe/Vienna'::text, machine_statuses.last_vend_timestamp))::date)::timestamp without time zone)), (timezone('Europe/Vienna'::text, ((timezone('Europe/Vienna'::text, machine_statuses.last_refill_timestamp))::date)::timestamp without time zone))
  • Sort Key: vends.machine_id, vends.product_id, (to_char((date(timezone('Europe/Vienna'::text, vends."timestamp")))::timestamp with time zone, 'ID'::text)), (timezone('Europe/Vienna'::text, ((timezone('Europe/Vienna'::text, machine_statuses.last_vend_timestamp))::date)::timestamp without time zone)), (timezone('Europe/Vienna'::text, ((timezone('Europe/Vienna'::text, machine_statuses.last_refill_timestamp))::date)::timestamp without time zone))
  • Sort Method: quicksort Memory: 2742kB
  • Buffers: shared hit=26361
9. 80.962 112.932 ↑ 2.1 25,267 1

Nested Loop (cost=1.58..198,285.27 rows=52,490 width=56) (actual time=0.068..112.932 rows=25,267 loops=1)

  • Output: to_char((date(timezone('Europe/Vienna'::text, vends."timestamp")))::timestamp with time zone, 'ID'::text), vends.machine_id, vends.product_id, timezone('Europe/Vienna'::text, ((timezone('Europe/Vienna'::text, machine_statuses.last_vend_timestamp))::date)::timestamp without time zone), timezone('Europe/Vienna'::text, ((timezone('Europe/Vienna'::text, machine_statuses.last_refill_timestamp))::date)::timestamp without time zone)
  • Buffers: shared hit=26361
10. 0.224 1.667 ↓ 1.4 117 1

Nested Loop (cost=0.99..750.54 rows=86 width=24) (actual time=0.026..1.667 rows=117 loops=1)

  • Output: machines.id, machine_statuses.last_vend_timestamp, machine_statuses.last_refill_timestamp, machines_1.id
  • Buffers: shared hit=843
11. 0.252 0.975 ↓ 1.4 117 1

Nested Loop (cost=0.57..462.60 rows=86 width=12) (actual time=0.018..0.975 rows=117 loops=1)

  • Output: machines.id, machines.status_id, machines_1.id
  • Buffers: shared hit=374
12. 0.138 0.138 ↓ 1.4 117 1

Index Only Scan using uidx_machines_owner_id__id on public.machines machines_1 (cost=0.29..38.01 rows=86 width=4) (actual time=0.011..0.138 rows=117 loops=1)

  • Output: machines_1.owner_id, machines_1.id
  • Index Cond: (machines_1.owner_id = 668)
  • Heap Fetches: 19
  • Buffers: shared hit=21
13. 0.585 0.585 ↑ 1.0 1 117

Index Scan using machines_pkey on public.machines (cost=0.29..4.93 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=117)

  • Output: machines.id, machines.status_id
  • Index Cond: (machines.id = machines_1.id)
  • Buffers: shared hit=353
14. 0.468 0.468 ↑ 1.0 1 117

Index Scan using machine_statuses_pkey on public.machine_statuses (cost=0.41..3.34 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=117)

  • Output: machine_statuses.last_vend_timestamp, machine_statuses.last_refill_timestamp, machine_statuses.id
  • Index Cond: (machine_statuses.id = machines.status_id)
  • Buffers: shared hit=469
15. 30.303 30.303 ↑ 6.2 216 117

Index Scan using idx_vends_machine_id__timestamp__includes on public.vends (cost=0.59..2,265.25 rows=1,335 width=16) (actual time=0.009..0.259 rows=216 loops=117)

  • Output: vends.id, vends.machine_id, vends.column_index, vends.vend_type, vends.quantity, vends.msg_id, vends.value, vends.product_id, vends."timestamp", vends.price_number, vends.transaction_id, vends.data, vends.failed, vends.banknotes, vends.coins, vends.device, vends.value_since_init, vends.quantity_since_init, vends.alive, vends.is_ghost, vends.ghost_column_id, vends.insert_time, vends.column_id, vends.card_id, vends.in_test_mode, vends._ver
  • Index Cond: ((vends.machine_id = machines.id) AND (vends."timestamp" >= (timezone('Europe/Vienna'::text, ((timezone('Europe/Vienna'::text, machine_statuses.last_vend_timestamp))::date)::timestamp without time zone) - '28 days'::interval)) AND (vends."timestamp" <= timezone('Europe/Vienna'::text, ((timezone('Europe/Vienna'::text, machine_statuses.last_vend_timestamp))::date)::timestamp without time zone)))
  • Buffers: shared hit=25518
16. 4.863 20.506 ↓ 2.7 5,041 1

Sort (cost=10,100.61..10,105.25 rows=1,856 width=16) (actual time=18.770..20.506 rows=5,041 loops=1)

  • Output: mc.machine_id, mc.product_id, (sum(mc.current_quantity))
  • Sort Key: mc.product_id, mc.machine_id
  • Sort Method: quicksort Memory: 429kB
  • Buffers: shared hit=2985
17. 5.354 15.643 ↓ 2.7 5,044 1

HashAggregate (cost=9,962.73..9,981.29 rows=1,856 width=16) (actual time=13.680..15.643 rows=5,044 loops=1)

  • Output: mc.machine_id, mc.product_id, sum(mc.current_quantity)
  • Group Key: mc.product_id, mc.machine_id
  • Buffers: shared hit=2985
18. 3.188 10.289 ↓ 3.3 6,151 1

Nested Loop (cost=0.71..9,948.81 rows=1,856 width=12) (actual time=0.031..10.289 rows=6,151 loops=1)

  • Output: mc.machine_id, mc.product_id, mc.current_quantity
  • Buffers: shared hit=2985
19. 0.081 0.081 ↓ 1.4 117 1

Index Only Scan using uidx_machines_owner_id__id on public.machines machines_2 (cost=0.29..38.01 rows=86 width=4) (actual time=0.014..0.081 rows=117 loops=1)

  • Output: machines_2.owner_id, machines_2.id
  • Index Cond: (machines_2.owner_id = 668)
  • Heap Fetches: 19
  • Buffers: shared hit=21
20. 7.020 7.020 ↓ 1.7 53 117

Index Scan using machine_columns_a9374927 on public.machine_columns mc (cost=0.43..114.93 rows=31 width=12) (actual time=0.006..0.060 rows=53 loops=117)

  • Output: mc.id, mc.index, mc.product_id, mc.current_quantity, mc.max_quantity, mc.warning_quantity, mc.alarm_quantity, mc.machine_id, mc.next_fill_quantity, mc.price, mc.is_custom_price, mc.is_discount_price, mc.alive, mc.notify_warning, mc.minimum_route_pickup, mc.changed_price, mc.pushed, mc.recipe_id, mc.combo_recipe_id, mc.blocked, mc.failed_vend_timestamp, mc.price_2, mc.changed_price_2, mc.freevend, mc.next_expiry, mc.today_loss_quantity, mc.view_index, mc.changed_index, mc.changed_price_3, mc.changed_price_4, mc.changed_price_5, mc.price_3, mc.price_4, mc.price_5, mc.changed_mapped_indexes, mc.mapped_indexes, mc.product_rotation, mc.product_rotation_group_id, mc._ver
  • Index Cond: (mc.machine_id = machines_2.id)
  • Filter: mc.pushed
  • Rows Removed by Filter: 10
  • Buffers: shared hit=2964
21. 12.111 12.111 ↑ 1.0 1 4,037

Index Scan using products_pkey on public.products p (cost=0.29..1.01 rows=1 width=14) (actual time=0.002..0.003 rows=1 loops=4,037)

  • Output: p.price, p.blacklisted, p.tax_rate_id, p.id
  • Index Cond: (p.id = s1.pid)
  • Filter: (p.alive AND (NOT p.is_composite) AND (p.owner_id = 668))
  • Buffers: shared hit=12111
22. 8.074 8.074 ↑ 1.0 1 4,037

Index Scan using tax_rates_pkey on public.tax_rates t (cost=0.28..0.66 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=4,037)

  • Output: t.id, t.owner_id, t.value, t.author_info_id, t."default", t.alive, t._ver
  • Index Cond: (t.id = p.tax_rate_id)
  • Buffers: shared hit=12111
Planning time : 2.769 ms
Execution time : 273.224 ms