explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6rb1 : prod prp query

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 52,656.999 ↓ 1.2 500 1

Limit (cost=230,623.50..230,624.56 rows=425 width=8,325) (actual time=52,656.952..52,656.999 rows=500 loops=1)

2. 73.088 52,656.965 ↓ 1.2 500 1

Sort (cost=230,623.50..230,624.56 rows=425 width=8,325) (actual time=52,656.946..52,656.965 rows=500 loops=1)

  • Sort Key: r.requestdate DESC, r.requestid DESC
  • Sort Method: top-N heapsort Memory: 785kB
3. 581.050 52,583.877 ↓ 188.2 79,986 1

WindowAgg (cost=228,038.57..230,604.95 rows=425 width=8,325) (actual time=52,485.419..52,583.877 rows=79,986 loops=1)

4. 156.013 52,002.827 ↓ 188.2 79,986 1

Nested Loop Left Join (cost=228,038.57..230,599.64 rows=425 width=1,363) (actual time=16,186.752..52,002.827 rows=79,986 loops=1)

5. 124.280 43,848.214 ↓ 188.6 79,986 1

Nested Loop Left Join (cost=228,038.13..230,381.52 rows=424 width=908) (actual time=16,186.731..43,848.214 rows=79,986 loops=1)

6. 64.033 18,368.372 ↓ 188.6 79,986 1

Nested Loop Left Join (cost=228,037.69..230,130.80 rows=424 width=453) (actual time=16,186.171..18,368.372 rows=79,986 loops=1)

7. 140.478 17,024.563 ↓ 188.6 79,986 1

Nested Loop (cost=228,037.26..229,927.04 rows=424 width=72) (actual time=16,186.095..17,024.563 rows=79,986 loops=1)

8. 32.145 16,244.197 ↓ 188.6 79,986 1

Unique (cost=228,036.82..228,038.94 rows=424 width=8) (actual time=16,186.036..16,244.197 rows=79,986 loops=1)

9. 51.990 16,212.052 ↓ 188.6 79,986 1

Sort (cost=228,036.82..228,037.88 rows=424 width=8) (actual time=16,186.033..16,212.052 rows=79,986 loops=1)

  • Sort Key: innerquery.requestid
  • Sort Method: quicksort Memory: 6822kB
10. 3.546 16,160.062 ↓ 188.6 79,986 1

Subquery Scan on innerquery (cost=227,964.61..228,018.32 rows=424 width=8) (actual time=16,137.467..16,160.062 rows=79,986 loops=1)

11. 26.474 16,156.516 ↓ 188.6 79,986 1

Gather Merge (cost=227,964.61..228,014.08 rows=424 width=16) (actual time=16,137.465..16,156.516 rows=79,986 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 41.234 16,130.042 ↓ 125.8 26,662 3 / 3

Sort (cost=226,964.58..226,965.11 rows=212 width=16) (actual time=16,128.077..16,130.042 rows=26,662 loops=3)

  • Sort Key: prc.requestdate DESC, prc.requestid DESC
  • Sort Method: quicksort Memory: 2023kB
  • Worker 0: Sort Method: quicksort Memory: 2013kB
  • Worker 1: Sort Method: quicksort Memory: 2019kB
13. 49.930 16,088.808 ↓ 125.8 26,662 3 / 3

Nested Loop (cost=1,315.79..226,956.39 rows=212 width=16) (actual time=53.078..16,088.808 rows=26,662 loops=3)

14. 2,477.823 2,494.582 ↑ 1.5 26,662 3 / 3

Parallel Bitmap Heap Scan on patron p_1 (cost=1,315.36..99,860.09 rows=38,860 width=8) (actual time=52.100..2,494.582 rows=26,662 loops=3)

  • Recheck Cond: (ppid = '26a16f8c-6407-475b-8249-1f0fb728ed68'::bpchar)
  • Heap Blocks: exact=15341
15. 16.759 16.759 ↑ 1.2 80,068 1 / 3

Bitmap Index Scan on patron_ppid_idx (cost=0.00..1,292.04 rows=93,264 width=0) (actual time=50.275..50.276 rows=80,068 loops=1)

  • Index Cond: (ppid = '26a16f8c-6407-475b-8249-1f0fb728ed68'::bpchar)
16. 13,544.296 13,544.296 ↑ 1.0 1 79,986 / 3

Index Scan using request_pkey on request prc (cost=0.43..3.27 rows=1 width=16) (actual time=0.508..0.508 rows=1 loops=79,986)

  • Index Cond: (requestid = p_1.requestid)
  • Filter: ((requestersymbol)::text = 'SOCBB'::text)
17. 639.888 639.888 ↑ 1.0 1 79,986

Index Scan using request_pkey on request r (cost=0.43..4.44 rows=1 width=72) (actual time=0.008..0.008 rows=1 loops=79,986)

  • Index Cond: (requestid = innerquery.requestid)
18. 1,279.776 1,279.776 ↑ 1.0 1 79,986

Index Scan using patron_requestid_key on patron p (cost=0.43..0.48 rows=1 width=389) (actual time=0.016..0.016 rows=1 loops=79,986)

  • Index Cond: (r.requestid = requestid)
19. 25,355.562 25,355.562 ↑ 1.0 1 79,986

Index Scan using requestidx on bib bm (cost=0.44..0.59 rows=1 width=463) (actual time=0.317..0.317 rows=1 loops=79,986)

  • Index Cond: (r.requestid = requestid)
  • Filter: (bibstatus = 2)
  • Rows Removed by Filter: 1
20. 7,998.600 7,998.600 ↑ 2.0 1 79,986

Index Scan using requestidx on bib br (cost=0.44..0.59 rows=2 width=463) (actual time=0.100..0.100 rows=1 loops=79,986)

  • Index Cond: (r.requestid = requestid)
  • Filter: (bibstatus = 1)
  • Rows Removed by Filter: 0
Planning time : 22.788 ms
Execution time : 52,666.653 ms