explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PLEb

Settings
# exclusive inclusive rows x rows loops node
1. 0.051 2,507.984 ↑ 1.0 100 1

Limit (cost=620,261.57..620,273.23 rows=100 width=1,420) (actual time=2,504.208..2,507.984 rows=100 loops=1)

  • Output: ln_json, ((ln_json ->> 'certificationStatus'::text)), ln_rqst_dttm, ((ln_json ->> 'investorLoanIdentifier'::text))
  • Buffers: shared hit=244890
2. 0.000 2,507.933 ↑ 2.3 100 1

Gather Merge (cost=620,261.57..620,288.87 rows=234 width=1,420) (actual time=2,504.206..2,507.933 rows=100 loops=1)

  • Output: ln_json, ((ln_json ->> 'certificationStatus'::text)), ln_rqst_dttm, ((ln_json ->> 'investorLoanIdentifier'::text))
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=244890
3. 77.175 7,505.343 ↑ 1.9 62 3

Sort (cost=619,261.54..619,261.84 rows=117 width=1,420) (actual time=2,501.763..2,501.781 rows=62 loops=3)

  • Output: ln_json, ((ln_json ->> 'certificationStatus'::text)), ln_rqst_dttm, ((ln_json ->> 'investorLoanIdentifier'::text))
  • Sort Key: ((ln.ln_json ->> 'certificationStatus'::text)), ln.ln_rqst_dttm, ((ln.ln_json ->> 'investorLoanIdentifier'::text))
  • Sort Method: top-N heapsort Memory: 379kB
  • Worker 0: Sort Method: top-N heapsort Memory: 363kB
  • Worker 1: Sort Method: top-N heapsort Memory: 363kB
  • Buffers: shared hit=244890
  • Worker 0: actual time=2500.773..2500.794 rows=70 loops=1
  • Buffers: shared hit=81367
  • Worker 1: actual time=2500.842..2500.864 rows=82 loops=1
  • Buffers: shared hit=80786
4. 7,353.233 7,428.168 ↓ 278.2 32,548 3

Parallel Bitmap Heap Scan on fkfdboa.ln (cost=7,983.18..619,257.52 rows=117 width=1,420) (actual time=88.546..2,476.056 rows=32,548 loops=3)

  • Output: ln_json, (ln_json ->> 'certificationStatus'::text), ln_rqst_dttm, (ln_json ->> 'investorLoanIdentifier'::text)
  • Recheck Cond: (((ln.ln_json ->> 'documentCustodian'::text) = '20000398668'::text) AND ((ln.ln_json ->> 'executionType'::text) = ANY ('{MBS,WholeLoan,ASAPPlus,ASAP}'::text[])))
  • Rows Removed by Index Recheck: 99957
  • Filter: ((ln.ln_crtfn_dttm >= '2019-09-02 00:00:00'::timestamp without time zone) AND (ln.ln_crtfn_dttm <= '2019-10-02 23:59:00'::timestamp without time zone) AND ((ln.ln_json ->> 'certificationStatus'::text) = ANY ('{Certified,QualifiedCertification}'::text[])) AND ((ln.ln_json ->> 'autoCertEligible'::text) = 'N'::text) AND ((ln.ln_json ->> 'rejectStatus'::text) = 'Not_Rejected'::text))
  • Rows Removed by Filter: 108238
  • Heap Blocks: exact=20967 lossy=44344
  • Buffers: shared hit=244856
  • Worker 0: actual time=88.436..2475.153 rows=31889 loops=1
  • Buffers: shared hit=81350
  • Worker 1: actual time=87.400..2475.013 rows=32187 loops=1
  • Buffers: shared hit=80769
5. 74.935 74.935 ↓ 1.4 422,356 1

Bitmap Index Scan on ln_ix10 (cost=0.00..7,983.11 rows=304,140 width=0) (actual time=74.935..74.935 rows=422,356 loops=1)

  • Index Cond: (((ln.ln_json ->> 'documentCustodian'::text) = '20000398668'::text) AND ((ln.ln_json ->> 'executionType'::text) = ANY ('{MBS,WholeLoan,ASAPPlus,ASAP}'::text[])))
  • Buffers: shared hit=2073
Planning time : 0.401 ms
Execution time : 2,508.056 ms