explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IxGS

Settings
# exclusive inclusive rows x rows loops node
1. 1.221 3,066.211 ↑ 102.8 40 1

Hash Left Join (cost=217,180.44..662,266.44 rows=4,114 width=325) (actual time=2,914.136..3,066.211 rows=40 loops=1)

  • Hash Cond: (ng.group_id = g.group_id)
  • Filter: (COALESCE(g.name, ''::text) ~ '.*'::text)
  • Buffers: shared hit=4147 read=236962
2.          

CTE x

3. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1)

4. 0.012 3,064.987 ↑ 16.2 40 1

Hash Left Join (cost=217,141.85..427,234.52 rows=648 width=333) (actual time=2,913.829..3,064.987 rows=40 loops=1)

  • Hash Cond: (p.trunk_id = t.trunk_id)
  • Buffers: shared hit=4145 read=236962
5. 0.202 3,064.972 ↑ 16.2 40 1

Hash Left Join (cost=217,112.73..427,196.49 rows=648 width=305) (actual time=2,913.821..3,064.972 rows=40 loops=1)

  • Hash Cond: ("substring"((de.args -> 'channel_name'::text), '(.*)-'::text) = p.name)
  • Buffers: shared hit=4145 read=236962
6. 0.023 3,064.765 ↑ 2.7 40 1

Nested Loop Left Join (cost=217,075.73..427,104.14 rows=108 width=301) (actual time=2,913.765..3,064.765 rows=40 loops=1)

  • Join Filter: (ng.num ~~ ('%'::text || (sed.args -> 'outgoing_cid_num'::text)))
  • Buffers: shared hit=4145 read=236962
7. 411.995 3,064.742 ↓ 2.4 40 1

Hash Right Join (cost=217,075.73..426,646.46 rows=17 width=297) (actual time=2,913.757..3,064.742 rows=40 loops=1)

  • Hash Cond: ((he.args -> 'channel_name'::text) = (s.args -> 'channel_name'::text))
  • Join Filter: ((he.dtime >= x.x_t_from) AND (he.dtime < x.x_t_to))
  • Rows Removed by Join Filter: 5
  • Buffers: shared hit=4145 read=236962
8. 1,076.728 1,076.728 ↑ 1.0 1,939,609 1

Seq Scan on call_details he (cost=0.00..182,856.10 rows=1,942,816 width=82) (actual time=0.046..1,076.728 rows=1,939,609 loops=1)

  • Filter: (dtype = 'Hangup'::text)
  • Rows Removed by Filter: 3164189
  • Buffers: shared hit=589 read=118465
9. 0.033 1,576.019 ↓ 2.4 40 1

Hash (cost=217,075.51..217,075.51 rows=17 width=305) (actual time=1,576.019..1,576.019 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=3556 read=118497
10. 0.028 1,575.986 ↓ 2.4 40 1

Nested Loop (cost=21,697.10..217,075.51 rows=17 width=305) (actual time=1,423.335..1,575.986 rows=40 loops=1)

  • Join Filter: ((ce.dtime >= x.x_t_from) AND (ce.dtime < x.x_t_to) AND (s.call_id = ce.call_id))
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=3556 read=118497
11. 412.066 1,575.548 ↓ 2.3 41 1

Hash Right Join (cost=21,696.54..216,696.22 rows=18 width=333) (actual time=1,423.304..1,575.548 rows=41 loops=1)

  • Hash Cond: ((hi.args -> 'channel_name'::text) = (ae.args -> 'channel_name'::text))
  • Join Filter: ((hi.dtime >= x.x_t_from) AND (hi.dtime < x.x_t_to))
  • Rows Removed by Join Filter: 39
  • Buffers: shared hit=3350 read=118497
12. 1,107.355 1,107.355 ↑ 1.0 1,939,609 1

Seq Scan on call_details hi (cost=0.00..182,856.10 rows=1,942,816 width=82) (actual time=0.076..1,107.355 rows=1,939,609 loops=1)

  • Filter: (dtype = 'Hangup'::text)
  • Rows Removed by Filter: 3164189
  • Buffers: shared hit=557 read=118497
13. 0.032 56.127 ↓ 2.3 41 1

Hash (cost=21,696.32..21,696.32 rows=18 width=399) (actual time=56.127..56.127 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
  • Buffers: shared hit=2793
14. 0.045 56.095 ↓ 2.3 41 1

Nested Loop Left Join (cost=14,991.31..21,696.32 rows=18 width=399) (actual time=54.322..56.095 rows=41 loops=1)

  • Join Filter: ((ae.dtime >= x.x_t_from) AND (ae.dtime < x.x_t_to))
  • Buffers: shared hit=2793
15. 0.042 55.722 ↓ 2.3 41 1

Nested Loop (cost=14,990.76..21,544.74 rows=18 width=317) (actual time=54.310..55.722 rows=41 loops=1)

  • Join Filter: ((de.dtime >= x.x_t_from) AND (de.dtime < x.x_t_to) AND (s.call_id = de.call_id))
  • Buffers: shared hit=2598
16. 0.012 55.229 ↑ 2.0 41 1

Nested Loop (cost=14,990.20..20,996.84 rows=82 width=225) (actual time=54.285..55.229 rows=41 loops=1)

  • Join Filter: ((sed.dtime >= x.x_t_from) AND (sed.dtime < x.x_t_to))
  • Buffers: shared hit=2392
17. 0.015 54.457 ↑ 5.3 76 1

Nested Loop (cost=14,989.64..17,618.05 rows=401 width=133) (actual time=54.254..54.457 rows=76 loops=1)

  • Buffers: shared hit=2045
18. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on x (cost=0.00..0.02 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=1)

19. 0.313 54.439 ↑ 5.3 76 1

Bitmap Heap Scan on call_details s (cost=14,989.64..17,614.02 rows=401 width=117) (actual time=54.247..54.439 rows=76 loops=1)

  • Recheck Cond: (((((args -> 'caller_type'::text) = ANY ('{internal,originate}'::text[])) AND (dtype = 'StartCall'::text)) OR (((args -> 'caller_type'::text) = 'external'::text) AND (dtype = 'StartCall'::text))) AND (dtime >= x.x_t_from) AND (dtime <= x.x_t_to))
  • Filter: ((party_num ~ '.*'::text) AND (((args -> 'caller_type'::text) = ANY ('{internal,originate}'::text[])) OR (((args -> 'caller_type'::text) = 'external'::text) AND ((args -> 'dialed_num'::text) ~~ '100%'::text) AND (length((args -> 'dialed_num'::text)) > 9))))
  • Rows Removed by Filter: 96
  • Heap Blocks: exact=32
  • Buffers: shared hit=2045
20. 9.490 54.126 ↓ 0.0 0 1

BitmapAnd (cost=14,989.64..14,989.64 rows=689 width=0) (actual time=54.126..54.126 rows=0 loops=1)

  • Buffers: shared hit=2013
21. 0.003 44.464 ↓ 0.0 0 1

BitmapOr (cost=181.56..181.56 rows=6,199 width=0) (actual time=44.464..44.464 rows=0 loops=1)

  • Buffers: shared hit=2005
22. 16.972 16.972 ↓ 32.6 134,871 1

Bitmap Index Scan on call_details_expr_idx (cost=0.00..119.83 rows=4,133 width=0) (actual time=16.972..16.972 rows=134,871 loops=1)

  • Index Cond: ((args -> 'caller_type'::text) = ANY ('{internal,originate}'::text[]))
  • Buffers: shared hit=653
23. 27.489 27.489 ↓ 134.4 277,581 1

Bitmap Index Scan on call_details_expr_idx (cost=0.00..59.92 rows=2,066 width=0) (actual time=27.489..27.489 rows=277,581 loops=1)

  • Index Cond: ((args -> 'caller_type'::text) = 'external'::text)
  • Buffers: shared hit=1352
24. 0.172 0.172 ↑ 343.7 1,650 1

Bitmap Index Scan on call_details_dtime_idx (cost=0.00..14,807.73 rows=567,130 width=0) (actual time=0.172..0.172 rows=1,650 loops=1)

  • Index Cond: ((dtime >= x.x_t_from) AND (dtime <= x.x_t_to))
  • Buffers: shared hit=8
25. 0.760 0.760 ↑ 1.0 1 76

Index Scan using call_details_call_id_dtype_expr_idx on call_details sed (cost=0.56..8.41 rows=1 width=100) (actual time=0.010..0.010 rows=1 loops=76)

  • Index Cond: ((call_id = s.call_id) AND (dtype = 'StartExtDial'::text))
  • Filter: (COALESCE((args -> 'outgoing_cid_num'::text), ''::text) ~ '.*'::text)
  • Buffers: shared hit=347
26. 0.451 0.451 ↑ 1.0 1 41

Index Scan using call_details_call_id_dtype_expr_idx on call_details de (cost=0.56..6.66 rows=1 width=100) (actual time=0.011..0.011 rows=1 loops=41)

  • Index Cond: ((call_id = sed.call_id) AND (dtype = 'DialExt'::text))
  • Filter: ("substring"((args -> 'channel_name'::text), '(.*)-'::text) ~ '.*'::text)
  • Buffers: shared hit=206
27. 0.328 0.328 ↑ 1.0 1 41

Index Scan using call_details_call_id_dtype_expr_idx on call_details ae (cost=0.56..8.41 rows=1 width=100) (actual time=0.008..0.008 rows=1 loops=41)

  • Index Cond: ((s.call_id = call_id) AND (dtype = 'AnsweredExternalCall'::text))
  • Buffers: shared hit=195
28. 0.410 0.410 ↑ 7.0 1 41

Index Scan using call_details_call_id_dtype_expr_idx on call_details ce (cost=0.56..20.95 rows=7 width=26) (actual time=0.009..0.010 rows=1 loops=41)

  • Index Cond: ((call_id = de.call_id) AND (dtype = 'EndCall'::text))
  • Buffers: shared hit=206
29. 0.000 0.000 ↓ 0.0 0 40

Materialize (cost=0.00..29.05 rows=1,270 width=36) (actual time=0.000..0.000 rows=0 loops=40)

30. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on numbers_groups ng (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.004..0.004 rows=0 loops=1)

31. 0.002 0.005 ↓ 0.0 0 1

Hash (cost=22.00..22.00 rows=1,200 width=36) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
32. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on peers p (cost=0.00..22.00 rows=1,200 width=36) (actual time=0.003..0.003 rows=0 loops=1)

33. 0.000 0.003 ↓ 0.0 0 1

Hash (cost=18.50..18.50 rows=850 width=36) (actual time=0.003..0.003 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
34. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on trunks t (cost=0.00..18.50 rows=850 width=36) (actual time=0.003..0.003 rows=0 loops=1)

35. 0.000 0.003 ↓ 0.0 0 1

Hash (cost=22.70..22.70 rows=1,270 width=36) (actual time=0.003..0.003 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
36. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on groups g (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.003..0.003 rows=0 loops=1)

37.          

SubPlan (forHash Left Join)

38. 0.000 0.000 ↓ 0.0 0 40

Seq Scan on working_hours wh (cost=0.00..56.75 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=40)

  • Filter: (((s.dtime)::time without time zone >= wt_from) AND ((s.dtime)::time without time zone <= wt_to) AND ((isodow)::double precision = date_part('isodow'::text, s.dtime)))