explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QtyV

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 184,069.195 ↑ 1.8 6 1

Limit (cost=2,977,830.54..2,977,832.46 rows=11 width=360) (actual time=184,053.200..184,069.195 rows=6 loops=1)

  • WHEN ((atscorp.scjscor >= '400'::numeric) AND (atscorp.scjscor < '500'::numeric)) THEN '400 to 499'::text WHEN ((atscorp.scjscor >= '500'::numeric) AND (atscorp.scjscor < '600'::numeric)) THEN '500 to 599'::text WHEN ((atscorp.scjscor >= '600'::numeric) AND (atscorp.scjscor < '700'::numeric)) THEN '600 to 699'::text WHEN ((atscorp.scjscor >= '700'::numeric) AND (atscorp.scjscor < '800'::numeric)) THEN '700 to 799'::text WHEN (atscorp.scjscor >= '800'::numeric) THEN '800 or Above'::text ELSE 'Undefined'::text END)
  • AND (atscorp.scjscor < '800'::numeric)) THEN '6'::text WHEN (atscorp.scjscor >= '800'::numeric) THEN '7'::text ELSE '8'::text END), (CASE WHEN (atscorp.scjscor < '200'::numeric) THEN 'Below 200'::text WHEN ((atscorp.scjscor >= '200'::numeric) AND (atscorp.scjscor < '300'::numeric)) THEN '200 to 299'::text WHEN ((atscorp.scjscor >= '300'::numeric) AND (atscorp.scjscor < '400'::numeric)) THEN '300 to 399'::text WHEN ((atscorp.scjscor >= '400'::numeric) AND (atscorp.scjscor < '500'::numeric)) THEN '400 to 499'::text WHEN ((atscorp.scjscor >= '500'::numeric) AND (atscorp.scjscor < '600'::numeric)) THEN '500to 599'::text WHEN ((atscorp.scjscor >= '600'::numeric) AND (atscorp.scjscor < '700'::numeric)) THEN '600 to 699'::text WHEN ((atscorp.scjscor >= '700'::numeric) AND (atscorp.scjscor < '800'::numeric)) THEN '700 to 799'::text WHEN (atscorp.scjscor >= '800'::numeric) THEN '800 or Above'::text ELSE 'Undefined'::text END)
2. 17.199 184,069.189 ↑ 1.8 6 1

GroupAggregate (cost=2,977,830.54..2,977,832.46 rows=11 width=360) (actual time=184,053.196..184,069.189 rows=6 loops=1)

  • Group Key: (CASE WHEN (atscorp.scjscor < '200'::numeric) THEN '0'::text WHEN ((atscorp.scjscor >= '200'::numeric) AND (atscorp.scjscor < '300'::numeric)) THEN '1'::text WHEN ((atscorp.scjscor >='300'::numeric) AND (atscorp.scjscor < '400'::numeric)) THEN '2'::text WHEN ((atscorp.scjscor >= '400'::numeric) AND (atscorp.scjscor < '500'::numeric)) THEN '3'::text WHEN ((atscorp.scjscor >= '500'::numeric) AND (atscorp.scjscor < '600'::numeric)) THEN '4'::text WHEN ((atscorp.scjscor >= '600'::numeric) AND (atscorp.scjscor < '700'::numeric)) THEN '5'::text WHEN ((atscorp.scjscor >= '700'::numeric) AND(atscorp.scjscor < '800'::numeric)) THEN '6'::text WHEN (atscorp.scjscor >= '800'::numeric) THEN '7'::text ELSE '8'::text END), (CASE WHEN (atscorp.scjscor < '200'::numeric) THEN 'Below 200'::text WHEN ((atscorp.scjscor >= '200'::numeric) AND (atscorp.scjscor < '300'::numeric)) THEN '200 to 299'::text WHEN ((atscorp.scjscor >= '300'::numeric) AND (atscorp.scjscor < '400'::numeric)) THEN '300 to 399'::text
3. 9.179 184,051.990 ↓ 747.0 8,217 1

Sort (cost=2,977,830.54..2,977,830.56 rows=11 width=100) (actual time=184,051.490..184,051.990 rows=8,217 loops=1)

  • Sort Key: (CASE WHEN (atscorp.scjscor < '200'::numeric) THEN '0'::text WHEN ((atscorp.scjscor >= '200'::numeric) AND (atscorp.scjscor < '300'::numeric)) THEN '1'::text WHEN ((atscorp.scjscor >= '300'::numeric) AND (atscorp.scjscor < '400'::numeric)) THEN '2'::text WHEN ((atscorp.scjscor >= '400'::numeric) AND (atscorp.scjscor < '500'::numeric)) THEN '3'::text WHEN ((atscorp.scjscor >= '500'::numeric) AND (atscorp.scjscor < '600'::numeric)) THEN '4'::text WHEN ((atscorp.scjscor >= '600'::numeric) AND (atscorp.scjscor < '700'::numeric)) THEN '5'::text WHEN ((atscorp.scjscor >= '700'::numeric)
  • Sort Method: quicksort Memory: 1540kB
4. 13,833.900 184,042.811 ↓ 747.0 8,217 1

Gather (cost=1,001.56..2,977,830.35 rows=11 width=100) (actual time=11,773.654..184,042.811 rows=8,217 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 9.793 170,208.911 ↓ 547.8 2,739 3 / 3

Nested Loop Left Join (cost=1.56..2,976,828.83 rows=5 width=100) (actual time=9,407.645..170,208.911 rows=2,739 loops=3)

6. 4.429 170,130.643 ↓ 547.8 2,739 3 / 3

Nested Loop (cost=1.13..2,976,811.19 rows=5 width=12) (actual time=9,407.410..170,130.643 rows=2,739 loops=3)

7. 1.864 170,043.257 ↓ 398.9 3,191 3 / 3

Nested Loop (cost=0.57..2,976,778.02 rows=8 width=8) (actual time=9,407.029..170,043.257 rows=3,191 loops=3)

8. 176.158 176.158 ↓ 13.5 27 3 / 3

Parallel Seq Scan on acctpf (cost=0.00..27,495.49 rows=2 width=6) (actual time=0.103..176.158 rows=27 loops=3)

  • Filter: ((hqtrs)::text = '00594'::text)
  • Rows Removed by Filter: 223012
9. 169,865.235 169,865.235 ↑ 1.9 118 81 / 3

Index Scan using idx_auto_atreqfpx__aqinv_aqacct_aqlnam_aqfnam on atreqfpx (cost=0.57..1,474,639.05 rows=221 width=14) (actual time=5,881.885..6,291.305 rows=118 loops=81)

  • Index Cond: ((aqacct)::text = (acctpf."ACCT#")::text)
  • Filter: ((aqdstm >= '1180101'::numeric) AND (aqdstm < '1190101'::numeric))
  • Rows Removed by Filter: 3050
10. 82.957 82.957 ↑ 1.0 1 9,572 / 3

Index Scan using idx_auto_atscorp__scinv on atscorp (cost=0.56..4.14 rows=1 width=13) (actual time=0.026..0.026 rows=1 loops=9,572)

  • Index Cond: (("SCINV#")::text = (atreqfpx."AQINV#")::text)
  • Filter: ((scjscor >= '200'::numeric) AND (scjscor < '800'::numeric))
  • Rows Removed by Filter: 0
11. 68.475 68.475 ↑ 1.0 1 8,217 / 3

Index Scan using idx_looker_creditprofile__trwinv on creditprofile l_crdtrw (cost=0.43..3.45 rows=1 width=45) (actual time=0.024..0.025 rows=1 loops=8,217)

  • Index Cond: ((trwinv)::text = (atreqfpx."AQINV#")::text)