explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ONdW : Optimization for: plan #iR8X

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.096 124,378.358 ↑ 1.0 25 1

Sort (cost=652,549.83..652,549.90 rows=25 width=866) (actual time=124,378.358..124,378.358 rows=25 loops=1)

  • Sort Key: r.datecreated, p.surname, p.firstname
  • Sort Method: quicksort Memory: 37kB
2. 0.095 124,378.262 ↑ 1.0 25 1

Nested Loop (cost=652,183.86..652,549.25 rows=25 width=866) (actual time=124,323.576..124,378.262 rows=25 loops=1)

3. 0.059 124,352.717 ↑ 1.0 25 1

Nested Loop (cost=652,183.43..652,398.11 rows=25 width=85) (actual time=124,323.542..124,352.717 rows=25 loops=1)

4. 0.011 124,323.308 ↑ 1.0 25 1

Limit (cost=652,182.86..652,182.93 rows=25 width=26) (actual time=124,323.282..124,323.308 rows=25 loops=1)

5. 1.765 124,323.297 ↑ 191.8 25 1

Sort (cost=652,182.86..652,194.85 rows=4,795 width=26) (actual time=124,323.282..124,323.297 rows=25 loops=1)

  • Sort Key: rr.datecreated, rr.surname, rr.firstname
  • Sort Method: top-N heapsort Memory: 26kB
6. 5.502 124,321.532 ↓ 2.2 10,777 1

WindowAgg (cost=650,992.50..652,047.55 rows=4,795 width=26) (actual time=124,319.708..124,321.532 rows=10,777 loops=1)

7. 1.356 124,316.030 ↓ 2.2 10,777 1

Subquery Scan on rr (cost=650,992.50..651,987.62 rows=4,795 width=26) (actual time=124,262.076..124,316.030 rows=10,777 loops=1)

8. 19.995 124,314.674 ↓ 2.2 10,777 1

Unique (cost=650,992.50..651,939.67 rows=4,795 width=30) (actual time=124,262.075..124,314.674 rows=10,777 loops=1)

9. 333.221 124,294.679 ↓ 1.1 199,209 1

Sort (cost=650,992.50..651,466.08 rows=189,434 width=30) (actual time=124,262.072..124,294.679 rows=199,209 loops=1)

  • Sort Key: reports.patientid, reports.datecreated, reports.id
  • Sort Method: quicksort Memory: 21713kB
10. 122,812.658 123,961.458 ↓ 1.1 199,209 1

Bitmap Heap Scan on reports (cost=4,602.91..634,387.34 rows=189,434 width=30) (actual time=1,198.920..123,961.458 rows=199,209 loops=1)

  • Recheck Cond: ((reporting_providerno)::text = ANY ('{0383153J,0383154X,220264ET,2353059Y,245386QW,2138317J,204847JJ,277331CX,0413837W,064397FW,0346423Y,064397DX,246437AB,014149NT,288748GF,446027DL,4727534H,0346424J,0413836H,425590PH,288748DX,5188089W,4461569Y,4727537T,406789QL,482546AF}'::text[]))
  • Heap Blocks: exact=142403
11. 1,148.800 1,148.800 ↓ 1.1 199,222 1

Bitmap Index Scan on reports_reporting_providerno (cost=0.00..4,555.56 rows=189,434 width=0) (actual time=1,148.800..1,148.800 rows=199,222 loops=1)

  • Index Cond: ((reporting_providerno)::text = ANY ('{0383153J,0383154X,220264ET,2353059Y,245386QW,2138317J,204847JJ,277331CX,0413837W,064397FW,0346423Y,064397DX,246437AB,014149NT,288748GF,446027DL,4727534H,0346424J,0413836H,425590PH,288748DX,5188089W,4461569Y,4727537T,406789QL,482546AF}'::text[]))
12. 29.350 29.350 ↑ 1.0 1 25

Index Scan using reports_id_patientid_idx on reports r (cost=0.57..8.59 rows=1 width=77) (actual time=1.173..1.174 rows=1 loops=25)

  • Index Cond: (id = rr.id)
13. 25.450 25.450 ↑ 1.0 1 25

Index Scan using patients_pkey on patients p (cost=0.43..6.04 rows=1 width=785) (actual time=1.017..1.018 rows=1 loops=25)

  • Index Cond: (id = r.patientid)
Planning time : 12.362 ms