explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RbX5

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 23,863.630 ↑ 1.0 1 1

Subquery Scan on f (cost=772,182.91..772,182.93 rows=1 width=32) (actual time=23,782.256..23,863.630 rows=1 loops=1)

2. 81.373 23,863.626 ↑ 1.0 1 1

Limit (cost=772,182.91..772,182.92 rows=1 width=8) (actual time=23,782.254..23,863.626 rows=1 loops=1)

3. 0.000 23,782.253 ↑ 1.0 1 1

Finalize Aggregate (cost=772,182.91..772,182.92 rows=1 width=8) (actual time=23,782.252..23,782.253 rows=1 loops=1)

4. 96.827 23,863.614 ↓ 1.5 3 1

Gather (cost=772,182.69..772,182.90 rows=2 width=8) (actual time=23,781.126..23,863.614 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.024 23,766.787 ↑ 1.0 1 3

Partial Aggregate (cost=771,182.69..771,182.70 rows=1 width=8) (actual time=23,766.786..23,766.787 rows=1 loops=3)

6. 360.894 23,766.763 ↑ 205.8 9 3

Hash Left Join (cost=17,026.75..771,178.06 rows=1,852 width=0) (actual time=4,516.990..23,766.763 rows=9 loops=3)

  • Hash Cond: ((tp.pat_id)::text = (isr.incoming_visit_id)::text)
  • Filter: (COALESCE(pr.center_id, isr.center_id) = ANY ('{0,22}'::integer[]))
  • Rows Removed by Filter: 261627
7. 15,668.017 23,405.869 ↓ 1.4 261,636 3

Nested Loop Left Join (cost=554.66..754,219.66 rows=185,258 width=19) (actual time=3.587..23,405.869 rows=261,636 loops=3)

8. 112.014 7,737.852 ↓ 1.4 261,636 3

Hash Join (cost=554.22..427,338.10 rows=185,258 width=15) (actual time=3.515..7,737.852 rows=261,636 loops=3)

  • Hash Cond: ((d.ddept_id)::text = (dd.ddept_id)::text)
9. 176.173 7,625.800 ↑ 1.2 305,744 3

Hash Join (cost=553.00..426,020.78 rows=370,516 width=25) (actual time=3.393..7,625.800 rows=305,744 loops=3)

  • Hash Cond: ((tp.test_id)::text = (d.test_id)::text)
10. 7,446.278 7,446.278 ↑ 1.2 305,744 3

Parallel Seq Scan on tests_prescribed tp (cost=0.00..424,493.02 rows=370,516 width=23) (actual time=0.029..7,446.278 rows=305,744 loops=3)

  • Filter: (((priority)::text = ANY ('{S,U}'::text[])) AND ((conducted)::text = 'S'::text))
  • Rows Removed by Filter: 3617466
11. 0.649 3.349 ↑ 1.0 1,911 3

Hash (cost=529.11..529.11 rows=1,911 width=18) (actual time=3.348..3.349 rows=1,911 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 110kB
12. 2.700 2.700 ↑ 1.0 1,911 3

Seq Scan on diagnostics d (cost=0.00..529.11 rows=1,911 width=18) (actual time=0.022..2.700 rows=1,911 loops=3)

13. 0.038 0.038 ↑ 1.0 6 3

Hash (cost=1.15..1.15 rows=6 width=10) (actual time=0.038..0.038 rows=6 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.000 0.093 ↑ 1.0 6 3

Seq Scan on diagnostics_departments dd (cost=0.00..1.15 rows=6 width=10) (actual time=0.026..0.031 rows=6 loops=3)

  • Filter: ((category)::text = 'DEP_LAB'::text)
  • Rows Removed by Filter: 6
15. 46,309.631 46,309.631 ↑ 1.0 1 784,909

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..1.76 rows=1 width=20) (actual time=0.059..0.059 rows=1 loops=784,909)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
16. 293.580 601.563 ↑ 1.0 382,671 3

Hash (cost=11,688.71..11,688.71 rows=382,671 width=13) (actual time=200.521..200.521 rows=382,671 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 22034kB
17. 307.983 307.983 ↑ 1.0 382,671 3

Seq Scan on incoming_sample_registration isr (cost=0.00..11,688.71 rows=382,671 width=13) (actual time=0.015..102.661 rows=382,671 loops=3)

Planning time : 3.965 ms
Execution time : 23,864.049 ms