explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DQ6O

Settings
# exclusive inclusive rows x rows loops node
1. 1.272 6,843.437 ↓ 1,187.0 1,187 1

HashAggregate (cost=2,722.72..2,722.73 rows=1 width=142) (actual time=6,843.283..6,843.437 rows=1,187 loops=1)

2.          

CTE raw_data

3. 13.633 233.917 ↓ 21,230.0 21,230 1

HashAggregate (cost=2,718.45..2,718.46 rows=1 width=35) (actual time=229.412..233.917 rows=21,230 loops=1)

4. 5.916 220.284 ↓ 21,230.0 21,230 1

Nested Loop (cost=12.56..2,718.44 rows=1 width=35) (actual time=0.199..220.284 rows=21,230 loops=1)

  • Join Filter: (hty.act_id = act.act_id)
  • Rows Removed by Join Filter: 785
5. 35.272 192.353 ↓ 22,015.0 22,015 1

Nested Loop (cost=12.56..2,717.36 rows=1 width=43) (actual time=0.195..192.353 rows=22,015 loops=1)

6. 12.993 76.686 ↓ 40,197.5 80,395 1

Nested Loop (cost=12.27..2,716.68 rows=2 width=27) (actual time=0.191..76.686 rows=80,395 loops=1)

7. 1.712 4.326 ↓ 257.0 257 1

Nested Loop Left Join (cost=11.85..2,520.47 rows=1 width=8) (actual time=0.101..4.326 rows=257 loops=1)

  • Join Filter: (tea.tea_id = tag.tea_id)
  • Rows Removed by Join Filter: 25208
  • Filter: (COALESCE(tea.tea_active, '1'::bpchar) = '1'::bpchar)
8. 0.180 1.586 ↓ 85.7 257 1

Hash Left Join (cost=11.85..2,514.01 rows=3 width=16) (actual time=0.074..1.586 rows=257 loops=1)

  • Hash Cond: (age.age_id = tag.age_id)
  • Filter: (COALESCE(tag.tag_active, '1'::bpchar) = '1'::bpchar)
9. 1.339 1.339 ↑ 2.0 257 1

Index Scan using user_pk on agent age (cost=0.40..2,499.19 rows=503 width=8) (actual time=0.003..1.339 rows=257 loops=1)

  • Filter: (age_active = '1'::bpchar)
  • Rows Removed by Filter: 648
10. 0.024 0.067 ↑ 1.0 241 1

Hash (cost=8.42..8.42 rows=242 width=18) (actual time=0.067..0.067 rows=241 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
11. 0.043 0.043 ↑ 1.0 241 1

Seq Scan on teamagent tag (cost=0.00..8.42 rows=242 width=18) (actual time=0.003..0.043 rows=241 loops=1)

12. 1.019 1.028 ↓ 1.2 99 257

Materialize (cost=0.00..2.26 rows=84 width=10) (actual time=0.000..0.004 rows=99 loops=257)

13. 0.009 0.009 ↓ 1.2 99 1

Seq Scan on team tea (cost=0.00..1.84 rows=84 width=10) (actual time=0.002..0.009 rows=99 loops=1)

14. 59.367 59.367 ↑ 2.1 313 257

Index Scan using task_age_id_in on task tsk (cost=0.42..189.79 rows=642 width=35) (actual time=0.005..0.231 rows=313 loops=257)

  • Index Cond: (age_id = age.age_id)
  • Filter: (tsk_active = '1'::bpchar)
  • Rows Removed by Filter: 11
15. 80.395 80.395 ↓ 0.0 0 80,395

Index Scan using history_tsk_id_in on history hty (cost=0.29..0.33 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=80,395)

  • Index Cond: (tsk_id = tsk.tsk_id)
  • Filter: ((hty_initialdatehour >= '2018-12-20 00:00:00'::timestamp without time zone) AND (hty_finaldatehour <= '2018-12-28 23:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 1
16. 22.015 22.015 ↑ 1.0 1 22,015

Seq Scan on activity act (cost=0.00..1.07 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=22,015)

  • Filter: (act_id = ANY ('{283490}'::integer[]))
  • Rows Removed by Filter: 5
17.          

CTE fields

18. 7.415 59.238 ↓ 2,374.0 2,374 1

Nested Loop (cost=0.42..4.20 rows=1 width=47) (actual time=0.254..59.238 rows=2,374 loops=1)

19. 9.363 9.363 ↓ 21,230.0 21,230 1

CTE Scan on raw_data rd_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..9.363 rows=21,230 loops=1)

20. 42.460 42.460 ↓ 0.0 0 21,230

Index Scan using historyvalue_acf_id_hty_id_in on historyvalue htv_super (cost=0.42..4.17 rows=1 width=47) (actual time=0.002..0.002 rows=0 loops=21,230)

  • Index Cond: ((acf_id = ANY ('{1370967,1347039}'::bigint[])) AND (hty_id = rd_1.hty_id))
21. 3,128.627 6,842.165 ↓ 2,374.0 2,374 1

Nested Loop (cost=0.00..0.05 rows=1 width=142) (actual time=311.040..6,842.165 rows=2,374 loops=1)

  • Join Filter: (rd.hty_id = f.hty_id)
  • Rows Removed by Join Filter: 50397646
22. 231.818 231.818 ↓ 21,230.0 21,230 1

CTE Scan on raw_data rd (cost=0.00..0.02 rows=1 width=134) (actual time=229.414..231.818 rows=21,230 loops=1)

23. 3,481.720 3,481.720 ↓ 2,374.0 2,374 21,230

CTE Scan on fields f (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.164 rows=2,374 loops=21,230)