explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kCyc

Settings
# exclusive inclusive rows x rows loops node
1. 4,440.531 4,440.531 ↑ 9.1 1,812 1

CTE Scan on report (cost=76,586,836.50..76,587,287.97 rows=16,417 width=697) (actual time=11.707..4,440.531 rows=1,812 loops=1)

2.          

CTE report

3. 8.656 4,429.385 ↑ 9.1 1,812 1

Hash Join (cost=493.97..76,586,836.50 rows=16,417 width=502) (actual time=11.697..4,429.385 rows=1,812 loops=1)

  • Hash Cond: (tracker.tracker_hnid = task.tracker_hnid)
4. 0.105 5.409 ↑ 43.1 42 1

Hash Left Join (cost=323.08..413.64 rows=1,812 width=200) (actual time=5.063..5.409 rows=42 loops=1)

  • Hash Cond: (insp.product_hnid = prod.product_hnid)
5. 0.121 5.151 ↑ 43.1 42 1

Hash Left Join (cost=314.90..380.58 rows=1,812 width=153) (actual time=4.903..5.151 rows=42 loops=1)

  • Hash Cond: (tracker.inspection_hnid = insp.inspection_hnid)
6. 0.938 3.712 ↑ 43.1 42 1

HashAggregate (cost=231.79..254.44 rows=1,812 width=60) (actual time=3.575..3.712 rows=42 loops=1)

  • Group Key: tracker.tracker_hnid, tracker.inspection_hnid, tracker.comments, insp_1.product_hnid
7. 0.997 2.774 ↑ 1.0 1,812 1

Hash Join (cost=70.79..191.02 rows=1,812 width=60) (actual time=0.955..2.774 rows=1,812 loops=1)

  • Hash Cond: (task_1.tracker_hnid = tracker.tracker_hnid)
8. 0.831 0.831 ↑ 1.0 1,812 1

Seq Scan on tracker_task task_1 (cost=0.00..95.32 rows=1,812 width=12) (actual time=0.001..0.831 rows=1,812 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 120
9. 0.010 0.946 ↑ 1.0 42 1

Hash (cost=70.26..70.26 rows=42 width=56) (actual time=0.946..0.946 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
10. 0.462 0.936 ↑ 1.0 42 1

Hash Right Join (cost=1.94..70.26 rows=42 width=56) (actual time=0.637..0.936 rows=42 loops=1)

  • Hash Cond: (insp_1.inspection_hnid = tracker.inspection_hnid)
11. 0.446 0.446 ↑ 1.0 1,738 1

Seq Scan on inspection insp_1 (cost=0.00..61.38 rows=1,738 width=16) (actual time=0.003..0.446 rows=1,738 loops=1)

12. 0.014 0.028 ↑ 1.0 42 1

Hash (cost=1.42..1.42 rows=42 width=48) (actual time=0.028..0.028 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
13. 0.014 0.014 ↑ 1.0 42 1

Seq Scan on tracker (cost=0.00..1.42 rows=42 width=48) (actual time=0.002..0.014 rows=42 loops=1)

  • Filter: (deleted IS FALSE)
14. 0.717 1.318 ↑ 1.0 1,738 1

Hash (cost=61.38..61.38 rows=1,738 width=112) (actual time=1.318..1.318 rows=1,738 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 251kB
15. 0.601 0.601 ↑ 1.0 1,738 1

Seq Scan on inspection insp (cost=0.00..61.38 rows=1,738 width=112) (actual time=0.002..0.601 rows=1,738 loops=1)

16. 0.088 0.153 ↑ 1.0 186 1

Hash (cost=5.86..5.86 rows=186 width=63) (actual time=0.153..0.153 rows=186 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
17. 0.065 0.065 ↑ 1.0 186 1

Seq Scan on product prod (cost=0.00..5.86 rows=186 width=63) (actual time=0.004..0.065 rows=186 loops=1)

18. 1.097 3.100 ↑ 1.0 1,812 1

Hash (cost=148.24..148.24 rows=1,812 width=310) (actual time=3.100..3.100 rows=1,812 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 590kB
19. 1.088 2.003 ↑ 1.0 1,812 1

Hash Left Join (cost=28.00..148.24 rows=1,812 width=310) (actual time=0.024..2.003 rows=1,812 loops=1)

  • Hash Cond: (task.group_hnid = grp.group_hnid)
20. 0.903 0.903 ↑ 1.0 1,812 1

Seq Scan on tracker_task task (cost=0.00..95.32 rows=1,812 width=278) (actual time=0.006..0.903 rows=1,812 loops=1)

  • Filter: (deleted IS FALSE)
  • Rows Removed by Filter: 120
21. 0.005 0.012 ↑ 80.0 10 1

Hash (cost=18.00..18.00 rows=800 width=40) (actual time=0.012..0.012 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.007 0.007 ↑ 80.0 10 1

Seq Scan on "group" grp (cost=0.00..18.00 rows=800 width=40) (actual time=0.003..0.007 rows=10 loops=1)

23.          

SubPlan (for Hash Join)

24. 3.624 4,412.220 ↑ 1.0 1 1,812

Aggregate (cost=4,665.00..4,665.01 rows=1 width=69) (actual time=2.435..2.435 rows=1 loops=1,812)

25. 10.872 4,408.596 ↓ 0.0 0 1,812

Sort (cost=4,664.76..4,664.79 rows=14 width=323) (actual time=2.433..2.433 rows=0 loops=1,812)

  • Sort Key: trackers._created_at DESC
  • Sort Method: quicksort Memory: 25kB
26. 3,745.404 4,397.724 ↓ 0.0 0 1,812

Bitmap Heap Scan on trackers (cost=138.02..4,664.49 rows=14 width=323) (actual time=2.427..2.427 rows=0 loops=1,812)

  • Recheck Cond: (type = 'file'::text)
  • Filter: ((("values" -> 'npi_tracker_task_hnid'::text) IS NOT NULL) AND ((("values" ->> 'npi_tracker_task_hnid'::text))::bigint = task.tracker_task_hnid))
  • Rows Removed by Filter: 2931
  • Heap Blocks: exact=3754464
27. 652.320 652.320 ↓ 1.0 3,022 1,812

Bitmap Index Scan on trackers_type_idx (cost=0.00..138.01 rows=2,897 width=0) (actual time=0.360..0.360 rows=3,022 loops=1,812)

  • Index Cond: (type = 'file'::text)
Planning time : 1.063 ms
Execution time : 4,441.412 ms