explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rAnA

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 23.971 ↓ 3.0 3 1

Limit (cost=74.03..74.03 rows=1 width=174) (actual time=23.970..23.971 rows=3 loops=1)

  • Output: d.id, d.mac, d.name, d.so_weight, (COALESCE(((d.so_weight / NULLIF((sum(d2.so_weight)), '0'::numeric)) * '100'::numeric), '0'::numeric)), d.do_weight, (COALESCE(((d.do_weight / NULLIF((sum(d2.do_weight)), '0'::numeric)) * '100'::numeric), '0'::numeric)), ((d.so_weight +
  • Buffers: shared hit=24632 read=1 dirtied=1
2.          

CTE summary

3. 0.013 23.869 ↓ 3.0 3 1

HashAggregate (cost=22.64..22.65 rows=1 width=38) (actual time=23.867..23.869 rows=3 loops=1)

  • Output: d_1.id, d_1.mac, d_1.device_main_name_id, sum(lmvfv2.so_weight), sum(lmvfv2.do_weight)
  • Group Key: d_1.id, d_1.mac, d_1.device_main_name_id
  • Buffers: shared hit=24618 read=1 dirtied=1
4. 0.010 23.856 ↓ 9.0 9 1

Nested Loop (cost=17.16..22.62 rows=1 width=38) (actual time=23.829..23.856 rows=9 loops=1)

  • Output: lmvfv2.so_weight, lmvfv2.do_weight, d_1.id, d_1.mac, d_1.device_main_name_id
  • Buffers: shared hit=24618 read=1 dirtied=1
5. 0.033 23.819 ↓ 9.0 9 1

Hash Right Join (cost=12.88..14.32 rows=1 width=20) (actual time=23.814..23.819 rows=9 loops=1)

  • Output: lmvfv2.so_weight, lmvfv2.do_weight, lmvfv2.device_id
  • Hash Cond: (p.id = lmvfv2.person_id)
  • Filter: (p.nickname IS NULL)
  • Rows Removed by Filter: 266
  • Buffers: shared hit=24582 read=1 dirtied=1
6. 0.010 0.010 ↓ 1.2 37 1

Seq Scan on public.person p (cost=0.00..1.31 rows=31 width=13) (actual time=0.005..0.010 rows=37 loops=1)

  • Output: p.id, p.nickname
  • Buffers: shared hit=1
7. 0.031 23.776 ↓ 27.5 275 1

Hash (cost=12.75..12.75 rows=10 width=24) (actual time=23.776..23.776 rows=275 loops=1)

  • Output: lmvfv2.so_weight, lmvfv2.do_weight, lmvfv2.device_id, lmvfv2.person_id
  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
  • Buffers: shared hit=24581 read=1 dirtied=1
8. 23.745 23.745 ↓ 27.5 275 1

Function Scan on public.flow_2 lmvfv2 (cost=0.25..12.75 rows=10 width=24) (actual time=23.681..23.745 rows=275 loops=1)

  • Output: lmvfv2.so_weight, lmvfv2.do_weight, lmvfv2.device_id, lmvfv2.person_id
  • Function Call: flow_2('1573167600'::bigint, '1573224662'::bigint)
  • Filter: (lmvfv2.ttr && '["2019-11-07 23:00:00+00","2019-11-08 14:51:02+00")'::tstzrange)
  • Buffers: shared hit=24581 read=1 dirtied=1
9. 0.009 0.027 ↑ 1.0 1 9

Bitmap Heap Scan on public.device d_1 (cost=4.28..8.29 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=9)

  • Output: d_1.id, d_1.mac, d_1.device_main_name_id
  • Recheck Cond: (d_1.id = lmvfv2.device_id)
  • Heap Blocks: exact=9
  • Buffers: shared hit=36
10. 0.018 0.018 ↑ 1.0 1 9

Bitmap Index Scan on idx_device_id_primary (cost=0.00..4.28 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=9)

  • Index Cond: (d_1.id = lmvfv2.device_id)
  • Buffers: shared hit=27
11.          

CTE s_name

12. 0.000 0.048 ↑ 2.2 5 1

WindowAgg (cost=50.63..50.96 rows=11 width=63) (actual time=0.043..0.048 rows=5 loops=1)

  • Output: s.id, COALESCE(dn.name, (s.mac)::text), row_number() OVER (?), ((s.device_main_name_id = dn.id)), dn.last_view_unix_time
  • Buffers: shared hit=14
13. 0.038 0.038 ↑ 2.2 5 1

Sort (cost=50.63..50.66 rows=11 width=63) (actual time=0.038..0.038 rows=5 loops=1)

  • Output: s.id, ((s.device_main_name_id = dn.id)), dn.last_view_unix_time, dn.name, s.mac
  • Sort Key: s.id, ((s.device_main_name_id = dn.id)) DESC, dn.last_view_unix_time DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=14
14. 0.007 0.038 ↑ 2.2 5 1

Sort (cost=50.63..50.66 rows=11 width=63) (actual time=0.038..0.038 rows=5 loops=1)

  • Output: s.id, ((s.device_main_name_id = dn.id)), dn.last_view_unix_time, dn.name, s.mac
  • Sort Key: s.id, ((s.device_main_name_id = dn.id)) DESC, dn.last_view_unix_time DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=14
15. 0.008 0.031 ↑ 2.2 5 1

Nested Loop Left Join (cost=8.49..50.44 rows=11 width=63) (actual time=0.013..0.031 rows=5 loops=1)

  • Output: s.id, (s.device_main_name_id = dn.id), dn.last_view_unix_time, dn.name, s.mac
  • Buffers: shared hit=14
16. 0.002 0.002 ↓ 3.0 3 1

CTE Scan on summary s (cost=0.00..0.02 rows=1 width=22) (actual time=0.000..0.002 rows=3 loops=1)

  • Output: s.id, s.mac, s.device_main_name_id, s.so_weight, s.do_weight
17. 0.006 0.021 ↑ 5.5 2 3

Bitmap Heap Scan on public.device_name dn (cost=8.49..50.31 rows=11 width=49) (actual time=0.006..0.007 rows=2 loops=3)

  • Output: dn.id, dn.device_id, dn.name, dn.source, dn.unix_time, dn.last_view_unix_time
  • Recheck Cond: (s.id = dn.device_id)
  • Heap Blocks: exact=5
  • Buffers: shared hit=14
18. 0.015 0.015 ↑ 5.5 2 3

Bitmap Index Scan on uq_device_name_name_source (cost=0.00..8.49 rows=11 width=0) (actual time=0.005..0.005 rows=2 loops=3)

  • Index Cond: (s.id = dn.device_id)
  • Buffers: shared hit=9
19.          

CTE data

20. 0.004 23.923 ↓ 3.0 3 1

Nested Loop (cost=0.00..0.28 rows=1 width=110) (actual time=23.919..23.923 rows=3 loops=1)

  • Output: s_1.id, s_1.mac, s1.name, s_1.so_weight, s_1.do_weight
  • Join Filter: (s_1.id = s1.id)
  • Rows Removed by Join Filter: 6
  • Buffers: shared hit=24632 read=1 dirtied=1
21. 23.868 23.868 ↓ 3.0 3 1

CTE Scan on summary s_1 (cost=0.00..0.02 rows=1 width=78) (actual time=23.868..23.868 rows=3 loops=1)

  • Output: s_1.id, s_1.mac, s_1.device_main_name_id, s_1.so_weight, s_1.do_weight
  • Buffers: shared hit=24618 read=1 dirtied=1
22. 0.051 0.051 ↓ 3.0 3 3

CTE Scan on s_name s1 (cost=0.00..0.25 rows=1 width=40) (actual time=0.015..0.017 rows=3 loops=3)

  • Output: s1.id, s1.name, s1.rank_name
  • Filter: (s1.rank_name = 1)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=14
23. 0.011 23.969 ↓ 3.0 3 1

Sort (cost=0.14..0.14 rows=1 width=174) (actual time=23.968..23.969 rows=3 loops=1)

  • Output: d.id, d.mac, d.name, d.so_weight, (COALESCE(((d.so_weight / NULLIF((sum(d2.so_weight)), '0'::numeric)) * '100'::numeric), '0'::numeric)), d.do_weight, (COALESCE(((d.do_weight / NULLIF((sum(d2.do_weight)), '0'::numeric)) * '100'::numeric), '0'::numeric)), ((d.so_weight + d.do_weight)), (COALESCE((((d.so_weight + d.do_weight) / NULLIF(((sum(d2.so_weight)) + (sum(d2.do_weight))), '0'::numeric)) * '100'::numeric), '0'::numeric)), (sum(d2.so_weight)), (sum(d2.do_weight)), (((sum(d2.so_weight)) + (sum(d2.do_weight)))), (count(*) OVER (?))
  • Sort Key: (COALESCE((((d.so_weight + d.do_weight) / NULLIF(((sum(d2.so_weight)) + (sum(d2.do_weight))), '0'::numeric)) * '100'::numeric), '0'::numeric)) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=24632 read=1 dirtied=1
24. 0.025 23.958 ↓ 3.0 3 1

WindowAgg (cost=0.03..0.12 rows=1 width=174) (actual time=23.952..23.958 rows=3 loops=1)

  • Output: d.id, d.mac, d.name, d.so_weight, COALESCE(((d.so_weight / NULLIF((sum(d2.so_weight)), '0'::numeric)) * '100'::numeric), '0'::numeric), d.do_weight, COALESCE(((d.do_weight / NULLIF((sum(d2.do_weight)), '0'::numeric)) * '100'::numeric), '0'::numeric), (d.so_weight + d.do_weight), COALESCE((((d.so_weight + d.do_weight) / NULLIF(((sum(d2.so_weight)) + (sum(d2.do_weight))), '0'::numeric)) * '100'::numeric), '0'::numeric), (sum(d2.so_weight)), (sum(d2.do_weight)), ((sum(d2.so_weight)) + (sum(d2.do_weight))), count(*) OVER (?)
  • Buffers: shared hit=24632 read=1 dirtied=1
25. 0.001 23.933 ↓ 3.0 3 1

Nested Loop (cost=0.03..0.08 rows=1 width=174) (actual time=23.930..23.933 rows=3 loops=1)

  • Output: d.id, d.mac, d.name, d.so_weight, (sum(d2.so_weight)), d.do_weight, (sum(d2.do_weight))
  • Buffers: shared hit=24632 read=1 dirtied=1
26. 23.920 23.920 ↓ 3.0 3 1

CTE Scan on data d (cost=0.00..0.02 rows=1 width=110) (actual time=23.920..23.920 rows=3 loops=1)

  • Output: d.id, d.mac, d.name, d.so_weight, d.do_weight
  • Buffers: shared hit=24632 read=1 dirtied=1
27. 0.006 0.012 ↑ 1.0 1 3

Aggregate (cost=0.03..0.04 rows=1 width=64) (actual time=0.004..0.004 rows=1 loops=3)

  • Output: sum(d2.so_weight), sum(d2.do_weight)
28. 0.006 0.006 ↓ 3.0 3 3

CTE Scan on data d2 (cost=0.00..0.02 rows=1 width=64) (actual time=0.000..0.002 rows=3 loops=3)

  • Output: d2.id, d2.mac, d2.name, d2.so_weight, d2.do_weight
Planning time : 0.714 ms
Execution time : 24.124 ms