explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l7TU : PRP

Settings
# exclusive inclusive rows x rows loops node
1. 0.033 76,935.879 ↓ 1.4 500 1

Limit (cost=193,234.87..193,235.77 rows=358 width=8,324) (actual time=76,935.830..76,935.879 rows=500 loops=1)

2. 78.757 76,935.846 ↓ 1.4 500 1

Sort (cost=193,234.87..193,235.77 rows=358 width=8,324) (actual time=76,935.826..76,935.846 rows=500 loops=1)

  • Sort Key: r.requestdate DESC, r.requestid DESC
  • Sort Method: top-N heapsort Memory: 792kB
3. 578.506 76,857.089 ↓ 219.7 78,646 1

WindowAgg (cost=191,040.25..193,219.69 rows=358 width=8,324) (actual time=76,662.751..76,857.089 rows=78,646 loops=1)

4. 142.942 76,278.583 ↓ 219.7 78,646 1

Nested Loop Left Join (cost=191,040.25..193,215.21 rows=358 width=1,366) (actual time=37,585.894..76,278.583 rows=78,646 loops=1)

5. 103.897 66,776.767 ↓ 219.7 78,646 1

Nested Loop Left Join (cost=191,039.82..192,995.89 rows=358 width=909) (actual time=37,585.873..66,776.767 rows=78,646 loops=1)

6. 49.338 39,540.000 ↓ 219.7 78,646 1

Nested Loop Left Join (cost=191,039.38..192,807.92 rows=358 width=452) (actual time=37,584.859..39,540.000 rows=78,646 loops=1)

7. 112.540 38,389.618 ↓ 219.7 78,646 1

Nested Loop (cost=191,038.94..192,635.87 rows=358 width=72) (actual time=37,584.811..38,389.618 rows=78,646 loops=1)

8. 34.991 37,647.910 ↓ 219.7 78,646 1

Unique (cost=191,038.51..191,040.30 rows=358 width=8) (actual time=37,584.762..37,647.910 rows=78,646 loops=1)

9. 49.043 37,612.919 ↓ 219.7 78,646 1

Sort (cost=191,038.51..191,039.40 rows=358 width=8) (actual time=37,584.758..37,612.919 rows=78,646 loops=1)

  • Sort Key: innerquery.requestid
  • Sort Method: quicksort Memory: 6759kB
10. 1.839 37,563.876 ↓ 219.7 78,646 1

Subquery Scan on innerquery (cost=190,977.97..191,023.32 rows=358 width=8) (actual time=37,540.266..37,563.876 rows=78,646 loops=1)

11. 69.878 37,562.037 ↓ 219.7 78,646 1

Gather Merge (cost=190,977.97..191,019.74 rows=358 width=16) (actual time=37,540.264..37,562.037 rows=78,646 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 45.427 37,492.159 ↓ 146.5 26,215 3 / 3

Sort (cost=189,977.95..189,978.40 rows=179 width=16) (actual time=37,489.621..37,492.159 rows=26,215 loops=3)

  • Sort Key: prc.requestdate DESC, prc.requestid DESC
  • Sort Method: quicksort Memory: 1994kB
  • Worker 0: Sort Method: quicksort Memory: 2008kB
  • Worker 1: Sort Method: quicksort Memory: 1990kB
13. 59.928 37,446.732 ↓ 146.5 26,215 3 / 3

Nested Loop (cost=1,037.25..189,971.25 rows=179 width=16) (actual time=23.377..37,446.732 rows=26,215 loops=3)

14. 9,423.769 9,441.259 ↑ 1.2 26,215 3 / 3

Parallel Bitmap Heap Scan on patron p_1 (cost=1,036.81..84,983.79 rows=30,551 width=8) (actual time=21.355..9,441.259 rows=26,215 loops=3)

  • Recheck Cond: (ppid = '26a16f8c-6407-475b-8249-1f0fb728ed68'::bpchar)
  • Heap Blocks: exact=14989
15. 17.490 17.490 ↓ 1.1 78,646 1 / 3

Bitmap Index Scan on patron_ppid_idx (cost=0.00..1,018.48 rows=73,323 width=0) (actual time=52.470..52.470 rows=78,646 loops=1)

  • Index Cond: (ppid = '26a16f8c-6407-475b-8249-1f0fb728ed68'::bpchar)
16. 27,945.545 27,945.545 ↑ 1.0 1 78,646 / 3

Index Scan using request_pkey on request prc (cost=0.43..3.44 rows=1 width=16) (actual time=1.066..1.066 rows=1 loops=78,646)

  • Index Cond: (requestid = p_1.requestid)
  • Filter: ((requestersymbol)::text = 'SOCBB'::text)
17. 629.168 629.168 ↑ 1.0 1 78,646

Index Scan using request_pkey on request r (cost=0.43..4.45 rows=1 width=72) (actual time=0.008..0.008 rows=1 loops=78,646)

  • Index Cond: (requestid = innerquery.requestid)
18. 1,101.044 1,101.044 ↑ 1.0 1 78,646

Index Scan using patron_requestid_key on patron p (cost=0.43..0.48 rows=1 width=388) (actual time=0.014..0.014 rows=1 loops=78,646)

  • Index Cond: (requestid = r.requestid)
19. 27,132.870 27,132.870 ↑ 2.0 1 78,646

Index Scan using requestidx on bib br (cost=0.44..0.61 rows=2 width=465) (actual time=0.345..0.345 rows=1 loops=78,646)

  • Index Cond: (requestid = r.requestid)
  • Filter: (bibstatus = 1)
  • Rows Removed by Filter: 0
20. 9,358.874 9,358.874 ↑ 1.0 1 78,646

Index Scan using requestidx on bib bm (cost=0.44..0.61 rows=1 width=465) (actual time=0.119..0.119 rows=1 loops=78,646)

  • Index Cond: (requestid = r.requestid)
  • Filter: (bibstatus = 2)
  • Rows Removed by Filter: 1
Planning time : 1.754 ms
Execution time : 76,943.687 ms