explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l8Z1u

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 3,745.205 ↓ 5.0 5 1

Limit (cost=32.71..32.71 rows=1 width=115) (actual time=3,745.205..3,745.205 rows=5 loops=1)

  • Output: c.id, (COALESCE(c.name, queue_domain_for_ident(lmvfv2.domain), 'Uncategorized'::text)), (sum((sum(lmvfv2.so_weight)))), ((((sum((sum(lmvfv2.so_weight)))) / sum((sum((sum(lmvfv2.so_weight))))) OVER (?)) * '100'::numeric)), (sum((sum(lmvfv2.do_weight)))), ((((sum((sum(lmvfv2.do_weight)))) / sum((sum((sum(lmvfv2.do_weight))))) OVER (?)) * '100'::numeric)), (sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight))))), ((((sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight))))) / sum((sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight)))))) OVER (?)) * '100'::numeric)), (sum((sum((sum(lmvfv2.so_weight))))) OVER (?)), (sum((sum((sum(lmvfv2.do_weight))))) OVER (?)), (sum((sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight)))))) OVER (?)), (count(*) OVER (?))
  • Buffers: shared hit=454596 read=37731, temp read=10264 written=10261
2. 0.039 3,745.204 ↓ 5.0 5 1

Sort (cost=32.71..32.71 rows=1 width=115) (actual time=3,745.204..3,745.204 rows=5 loops=1)

  • Output: c.id, (COALESCE(c.name, queue_domain_for_ident(lmvfv2.domain), 'Uncategorized'::text)), (sum((sum(lmvfv2.so_weight)))), ((((sum((sum(lmvfv2.so_weight)))) / sum((sum((sum(lmvfv2.so_weight))))) OVER (?)) * '100'::numeric)), (sum((sum(lmvfv2.do_weight)))), ((((sum((sum(lmvfv2.do_weight)))) / sum((sum((sum(lmvfv2.do_weight))))) OVER (?)) * '100'::numeric)), (sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight))))), ((((sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight))))) / sum((sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight)))))) OVER (?)) * '100'::numeric)), (sum((sum((sum(lmvfv2.so_weight))))) OVER (?)), (sum((sum((sum(lmvfv2.do_weight))))) OVER (?)), (sum((sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight)))))) OVER (?)), (count(*) OVER (?))
  • Sort Key: (sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight))))) DESC
  • Sort Method: top-N heapsort Memory: 26kB
  • Buffers: shared hit=454596 read=37731, temp read=10264 written=10261
3. 0.173 3,745.165 ↓ 71.0 71 1

WindowAgg (cost=32.10..32.70 rows=1 width=115) (actual time=3,745.059..3,745.165 rows=71 loops=1)

  • Output: c.id, (COALESCE(c.name, queue_domain_for_ident(lmvfv2.domain), 'Uncategorized'::text)), (sum((sum(lmvfv2.so_weight)))), (((sum((sum(lmvfv2.so_weight)))) / sum((sum((sum(lmvfv2.so_weight))))) OVER (?)) * '100'::numeric), (sum((sum(lmvfv2.do_weight)))), (((sum((sum(lmvfv2.do_weight)))) / sum((sum((sum(lmvfv2.do_weight))))) OVER (?)) * '100'::numeric), (sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight))))), (((sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight))))) / sum((sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight)))))) OVER (?)) * '100'::numeric), sum((sum((sum(lmvfv2.so_weight))))) OVER (?), sum((sum((sum(lmvfv2.do_weight))))) OVER (?), sum((sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight)))))) OVER (?), count(*) OVER (?)
  • Buffers: shared hit=454596 read=37731, temp read=10264 written=10261
4. 6.345 3,744.992 ↓ 71.0 71 1

HashAggregate (cost=32.10..32.41 rows=1 width=115) (actual time=3,744.974..3,744.992 rows=71 loops=1)

  • Output: c.id, (COALESCE(c.name, queue_domain_for_ident(lmvfv2.domain), 'Uncategorized'::text)), sum((sum(lmvfv2.so_weight))), sum((sum(lmvfv2.do_weight))), sum(((sum(lmvfv2.so_weight)) + (sum(lmvfv2.do_weight))))
  • Group Key: c.id, COALESCE(c.name, queue_domain_for_ident(lmvfv2.domain), 'Uncategorized'::text)
  • Buffers: shared hit=454596 read=37731, temp read=10264 written=10261
5. 7.204 3,738.647 ↓ 8,692.0 8,692 1

Nested Loop Left Join (cost=31.76..32.06 rows=1 width=115) (actual time=3,666.790..3,738.647 rows=8,692 loops=1)

  • Output: c.id, COALESCE(c.name, queue_domain_for_ident(lmvfv2.domain), 'Uncategorized'::text), (sum(lmvfv2.so_weight)), (sum(lmvfv2.do_weight))
  • Buffers: shared hit=454596 read=37731, temp read=10264 written=10261
6. 98.238 3,670.599 ↓ 8,692.0 8,692 1

HashAggregate (cost=14.96..14.97 rows=1 width=48) (actual time=3,666.705..3,670.599 rows=8,692 loops=1)

  • Output: lmvfv2.domain, sum(lmvfv2.so_weight), sum(lmvfv2.do_weight)
  • Group Key: lmvfv2.domain
  • Buffers: shared hit=382980 read=37727, temp read=10264 written=10261
7. 312.873 3,572.361 ↓ 355,366.0 355,366 1

Nested Loop (cost=0.25..14.95 rows=1 width=48) (actual time=3,113.940..3,572.361 rows=355,366 loops=1)

  • Output: lmvfv2.domain, lmvfv2.so_weight, lmvfv2.do_weight
  • Buffers: shared hit=382980 read=37727, temp read=10264 written=10261
8. 85.870 3,259.488 ↓ 355,366.0 355,366 1

Nested Loop (cost=0.25..13.91 rows=1 width=52) (actual time=3,113.921..3,259.488 rows=355,366 loops=1)

  • Output: lmvfv2.domain, lmvfv2.so_weight, lmvfv2.do_weight, l.entity_id
  • Join Filter: (lmvfv2.link_id = l.id)
  • Rows Removed by Join Filter: 355366
  • Buffers: shared hit=27614 read=37727, temp read=10264 written=10261
9. 0.014 0.014 ↓ 2.0 2 1

Seq Scan on public.link l (cost=0.00..1.04 rows=1 width=8) (actual time=0.004..0.014 rows=2 loops=1)

  • Output: l.id, l.entity_id
  • Filter: (l.entity_id = 7)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
10. 3,173.604 3,173.604 ↓ 35,536.6 355,366 2

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

  • 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=27613 read=37727, temp read=10264 written=10261
11. 0.000 0.000 ↑ 1.0 1 355,366

Seq Scan on public.entity e (cost=0.00..1.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=355,366)

  • Output: e.id, e.name, e.timezone, e.address1, e.address2, e.address3, e.city, e.country, e.zipcode
  • Filter: (e.id = 7)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=355366
12. 0.000 60.844 ↑ 1.0 1 8,692

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

  • Output: c.id, c.name, cd.weight
  • Buffers: shared hit=71616 read=4
13. 8.692 60.844 ↑ 1.0 1 8,692

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

  • Output: c.id, c.name, cd.weight
  • Sort Key: cd.weight DESC, c.id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=71616 read=4
14. 0.000 52.152 ↑ 1.0 1 8,692

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

  • Output: c.id, c.name, cd.weight
  • Buffers: shared hit=71616 read=4
15. 8.692 43.460 ↑ 1.0 1 8,692

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

  • Output: cd.weight, cd.class_id
  • Buffers: shared hit=52308 read=4
16. 26.076 26.076 ↑ 1.0 1 8,692

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=8,692)

  • Output: d.id, d.domain
  • Index Cond: (d.domain = lmvfv2.domain)
  • Buffers: shared hit=26180
17. 8.692 8.692 ↑ 1.0 1 8,692

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=8,692)

  • Output: cd.class_id, cd.domain_id, cd.weight, cd.source, cd.ts
  • Index Cond: (cd.domain_id = d.id)
  • Buffers: shared hit=26128 read=4
18. 9.654 9.654 ↑ 1.0 1 9,654

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=9,654)

  • Output: c.id, c.name
  • Index Cond: (cd.class_id = c.id)
  • Buffers: shared hit=19308
Planning time : 0.615 ms
Execution time : 3,746.818 ms