explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JVfTA

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 5,766.342 ↓ 2.5 5 1

Limit (cost=185.16..185.17 rows=2 width=67) (actual time=5,766.341..5,766.342 rows=5 loops=1)

  • Output: c.id, (COALESCE(c.name, queue_domain_for_ident(lmvfv2.domain), 'Uncategorized'::text)), (sum(lmvfv2.so_weight)), ((((sum(lmvfv2.so_weight)) / sum((sum(lmvfv2.so_weight))) OVER (?)) * '100'::numeric)), (sum(lmvfv2.do_weight)), ((((sum(lmvfv2.do_weight)) / sum((sum(lmvfv2.do_weight))) OVER (?)) * '100'::numeric)), (sum((lmvfv2.so_weight + lmvfv2.do_weight))), ((((sum((lmvfv2.so_weight + lmvfv2.do_weight))) / sum((sum((lmvfv2.so_weight + lmvfv2.do_weight)))) OVER (?)) * '100'::numeric)), (sum((sum(lmvfv2.so_weight))) OVER (?)), (sum((sum(lmvfv2.do_weight))) OVER (?)), (sum((sum((lmvfv2.so_weight + lmvfv2.do_weight)))) OVER (?)), (count(*) OVER (?))
  • Buffers: shared hit=2981732 read=38685, temp read=6844 written=10261
2. 0.037 5,766.340 ↓ 2.5 5 1

Sort (cost=185.16..185.17 rows=2 width=67) (actual time=5,766.340..5,766.340 rows=5 loops=1)

  • Output: c.id, (COALESCE(c.name, queue_domain_for_ident(lmvfv2.domain), 'Uncategorized'::text)), (sum(lmvfv2.so_weight)), ((((sum(lmvfv2.so_weight)) / sum((sum(lmvfv2.so_weight))) OVER (?)) * '100'::numeric)), (sum(lmvfv2.do_weight)), ((((sum(lmvfv2.do_weight)) / sum((sum(lmvfv2.do_weight))) OVER (?)) * '100'::numeric)), (sum((lmvfv2.so_weight + lmvfv2.do_weight))), ((((sum((lmvfv2.so_weight + lmvfv2.do_weight))) / sum((sum((lmvfv2.so_weight + lmvfv2.do_weight)))) OVER (?)) * '100'::numeric)), (sum((sum(lmvfv2.so_weight))) OVER (?)), (sum((sum(lmvfv2.do_weight))) OVER (?)), (sum((sum((lmvfv2.so_weight + lmvfv2.do_weight)))) OVER (?)), (count(*) OVER (?))
  • Sort Key: (sum((lmvfv2.so_weight + lmvfv2.do_weight))) DESC
  • Sort Method: top-N heapsort Memory: 26kB
  • Buffers: shared hit=2981732 read=38685, temp read=6844 written=10261
3. 0.170 5,766.303 ↓ 35.5 71 1

WindowAgg (cost=183.96..185.15 rows=2 width=67) (actual time=5,766.194..5,766.303 rows=71 loops=1)

  • Output: c.id, (COALESCE(c.name, queue_domain_for_ident(lmvfv2.domain), 'Uncategorized'::text)), (sum(lmvfv2.so_weight)), (((sum(lmvfv2.so_weight)) / sum((sum(lmvfv2.so_weight))) OVER (?)) * '100'::numeric), (sum(lmvfv2.do_weight)), (((sum(lmvfv2.do_weight)) / sum((sum(lmvfv2.do_weight))) OVER (?)) * '100'::numeric), (sum((lmvfv2.so_weight + lmvfv2.do_weight))), (((sum((lmvfv2.so_weight + lmvfv2.do_weight))) / sum((sum((lmvfv2.so_weight + lmvfv2.do_weight)))) OVER (?)) * '100'::numeric), sum((sum(lmvfv2.so_weight))) OVER (?), sum((sum(lmvfv2.do_weight))) OVER (?), sum((sum((lmvfv2.so_weight + lmvfv2.do_weight)))) OVER (?), count(*) OVER (?)
  • Buffers: shared hit=2981732 read=38685, temp read=6844 written=10261
4. 148.321 5,766.133 ↓ 35.5 71 1

HashAggregate (cost=183.96..184.57 rows=2 width=67) (actual time=5,766.110..5,766.133 rows=71 loops=1)

  • Output: c.id, (COALESCE(c.name, queue_domain_for_ident(lmvfv2.domain), 'Uncategorized'::text)), sum(lmvfv2.so_weight), sum(lmvfv2.do_weight), sum((lmvfv2.so_weight + lmvfv2.do_weight))
  • Group Key: c.id, COALESCE(c.name, queue_domain_for_ident(lmvfv2.domain), 'Uncategorized'::text)
  • Buffers: shared hit=2981732 read=38685, temp read=6844 written=10261
5. 134.448 5,617.812 ↓ 35,536.6 355,366 1

Nested Loop Left Join (cost=17.05..183.51 rows=10 width=67) (actual time=2,893.068..5,617.812 rows=355,366 loops=1)

  • Output: c.id, COALESCE(c.name, queue_domain_for_ident(lmvfv2.domain), 'Uncategorized'::text), lmvfv2.so_weight, lmvfv2.do_weight
  • Buffers: shared hit=2981732 read=38685, temp read=6844 written=10261
6. 2,995.802 2,995.802 ↓ 35,536.6 355,366 1

Function Scan on public.flow_2 lmvfv2 (cost=0.25..12.75 rows=10 width=48) (actual time=2,892.384..2,995.802 rows=355,366 loops=1)

  • Output: lmvfv2.ttr, lmvfv2.person_id, lmvfv2.device_id, lmvfv2.link_id, lmvfv2.domain, lmvfv2.so_weight, lmvfv2.do_weight
  • Function Call: flow_2('1565042400'::bigint, '1573048241'::bigint)
  • Filter: (lmvfv2.ttr && '["2019-08-05 22:00:00+00","2019-11-06 13:50:41+00")'::tstzrange)
  • Buffers: shared hit=27222 read=38138, temp read=6844 written=10261
7. 0.000 2,487.562 ↑ 1.0 1 355,366

Limit (cost=16.80..16.81 rows=1 width=23) (actual time=0.007..0.007 rows=1 loops=355,366)

  • Output: c.id, c.name, cd.weight
  • Buffers: shared hit=2954510 read=547
8. 355.366 2,487.562 ↑ 1.0 1 355,366

Sort (cost=16.80..16.81 rows=1 width=23) (actual time=0.007..0.007 rows=1 loops=355,366)

  • Output: c.id, c.name, cd.weight
  • Sort Key: cd.weight DESC, c.id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2954510 read=547
9. 306.991 2,132.196 ↑ 1.0 1 355,366

Nested Loop Left Join (cost=0.72..16.79 rows=1 width=23) (actual time=0.005..0.006 rows=1 loops=355,366)

  • Output: c.id, c.name, cd.weight
  • Buffers: shared hit=2954510 read=547
10. 0.000 1,421.464 ↑ 1.0 1 355,366

Nested Loop Left Join (cost=0.57..16.62 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=355,366)

  • Output: cd.weight, cd.class_id
  • Buffers: shared hit=2147030 read=545
11. 1,066.098 1,066.098 ↑ 1.0 1 355,366

Index Scan using uq_domain_domain on public.domain d (cost=0.29..8.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=355,366)

  • Output: d.id, d.domain
  • Index Cond: (d.domain = lmvfv2.domain)
  • Buffers: shared hit=1078719 read=291
12. 355.366 355.366 ↑ 1.0 1 355,366

Index Scan using fk_class_domain_domain_id_idx on public.class_domain cd (cost=0.29..8.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=355,366)

  • Output: cd.class_id, cd.domain_id, cd.weight, cd.source, cd.ts
  • Index Cond: (cd.domain_id = d.id)
  • Buffers: shared hit=1068311 read=254
13. 403.741 403.741 ↑ 1.0 1 403,741

Index Scan using idx_class_id_primary on public.class c (cost=0.14..0.16 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=403,741)

  • Output: c.id, c.name
  • Index Cond: (cd.class_id = c.id)
  • Buffers: shared hit=807480 read=2
Planning time : 0.674 ms
Execution time : 5,769.619 ms