explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gdGM

Settings
# exclusive inclusive rows x rows loops node
1. 26,043.314 488,825.736 ↑ 86.1 2,644 1

GroupAggregate (cost=3,279,791.12..3,294,026.55 rows=227,767 width=38) (actual time=386,093.820..488,825.736 rows=2,644 loops=1)

2. 281,027.373 462,782.422 ↓ 25.4 5,774,112 1

Sort (cost=3,279,791.12..3,280,360.53 rows=227,767 width=38) (actual time=386,093.742..462,782.422 rows=5,774,112 loops=1)

  • Sort Key: sg_rsc_d2.sg_rsc2_sku, sg_dlr_m1.sg_dlr_agent, (CASE WHEN ((sg_con_m1.sg_con_term >= 0) AND (sg_con_m1.sg_con_term <= 60)) THEN '0 to 60 Months'::text WHEN ((sg_con_m1.sg_con_term >= 61) AND (sg_con_m1.sg_con_term <= 72)) THEN '61 to 72 Months'::text WHEN ((sg_con_m1.sg_con_term >= 73) AND (sg_con_m1.sg_con_term <= 84)) THEN '73 to 84 Months'::text ELSE '85+ Months'::text END)
  • Sort Method: external merge Disk: 430120kB
3. 9,081.415 181,755.049 ↓ 25.4 5,774,112 1

Hash Join (cost=3,218,125.60..3,259,523.04 rows=227,767 width=38) (actual time=126,612.521..181,755.049 rows=5,774,112 loops=1)

  • Hash Cond: (sg_con_m1.sg_con_dealer = sg_dlr_m1.sg_dlr_dealer)
4. 17,592.511 172,569.348 ↓ 25.4 5,774,112 1

Merge Join (cost=3,211,696.23..3,239,712.36 rows=227,767 width=40) (actual time=126,508.191..172,569.348 rows=5,774,112 loops=1)

  • Merge Cond: ((sg_con_m1.sg_con_rsseq = sg_rsc_d2.sg_rsc2_seq) AND ((sg_con_m1.sg_con_rs)::bpchar = sg_rsc_d2.sg_rsc2_rs))
5. 110,008.893 152,107.848 ↓ 1.3 4,573,692 1

Sort (cost=3,192,444.77..3,200,936.28 rows=3,396,606 width=39) (actual time=126,403.614..152,107.848 rows=4,573,692 loops=1)

  • Sort Key: sg_con_m1.sg_con_rsseq, sg_con_m1.sg_con_rs
  • Sort Method: external merge Disk: 262504kB
6. 17,346.880 42,098.955 ↓ 1.5 5,144,331 1

Hash Join (cost=219,432.79..2,769,587.88 rows=3,396,606 width=39) (actual time=4,055.452..42,098.955 rows=5,144,331 loops=1)

  • Hash Cond: ((sg_con_m1.sg_con_plc)::bpchar = sg_plc_m1.sg_plc_plc)
7. 20,876.693 24,751.926 ↑ 1.0 20,315,050 1

Bitmap Heap Scan on sg_con_m1 (cost=219,413.59..2,591,246.86 rows=20,379,639 width=43) (actual time=4,055.115..24,751.926 rows=20,315,050 loops=1)

  • Recheck Cond: (sg_con_saledate >= '2012-01-01'::date)
8. 3,875.233 3,875.233 ↑ 1.0 20,315,553 1

Bitmap Index Scan on idx_sg_con_m1_substring_saledate (cost=0.00..214,318.68 rows=20,379,639 width=0) (actual time=3,875.233..3,875.233 rows=20,315,553 loops=1)

  • Index Cond: (sg_con_saledate >= '2012-01-01'::date)
9. 0.043 0.149 ↑ 1.0 58 1

Hash (cost=17.31..17.31 rows=58 width=5) (actual time=0.149..0.149 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
10. 0.106 0.106 ↑ 1.0 58 1

Seq Scan on sg_plc_m1 (cost=0.00..17.31 rows=58 width=5) (actual time=0.018..0.106 rows=58 loops=1)

  • Filter: ((sg_plc_rstype)::text = 'G'::text)
11. 2,851.603 2,868.989 ↓ 498.6 5,780,776 1

Sort (cost=13,284.05..13,313.03 rows=11,593 width=26) (actual time=100.136..2,868.989 rows=5,780,776 loops=1)

  • Sort Key: sg_rsc_d2.sg_rsc2_seq, sg_rsc_d2.sg_rsc2_rs
  • Sort Method: quicksort Memory: 1324kB
12. 15.195 17.386 ↓ 1.0 12,021 1

Bitmap Heap Scan on sg_rsc_d2 (cost=148.80..12,501.47 rows=11,593 width=26) (actual time=2.379..17.386 rows=12,021 loops=1)

  • Recheck Cond: ((sg_rsc2_sku)::text = ANY ('{PPGP-FUCCILLO,PPGP-NATL,PPGP-PL-NATL,PPGP-TX,G-A-F-GAP125-OR,G-A-F-GAP-ACV-OR-16,G-A-F-GAP-ACV-OR,G-A-F-GAP-COCC-OR,G-A-F-GAPCOMM-OR-16,G-A-F-GAPCOMM-OR,G-A-F-GAPCOMPL-OR,G-A-F-GAP-OR-16,G-A-F-GAP-OR,G-A-F-GAPPLUS-OR-16,G-A-F-GAPPLUS-OR,G-A-GAP150-OR-TC,G-A-GAP-CONICELLI-OR,G-A-GAPI-NV-OR,G-A-I-GAP125-OR,G-A-I-GAP-COCC-OR,G-A-I-GAPCOMM-OR,G-A-I-GAP-OR-16,G-A-I-GAP-OR,G-A-I-GAPPLUS-OR}'::text[]))
13. 2.191 2.191 ↓ 1.0 12,021 1

Bitmap Index Scan on sg_rsc_d202 (cost=0.00..145.90 rows=11,593 width=0) (actual time=2.191..2.191 rows=12,021 loops=1)

  • Index Cond: ((sg_rsc2_sku)::text = ANY ('{PPGP-FUCCILLO,PPGP-NATL,PPGP-PL-NATL,PPGP-TX,G-A-F-GAP125-OR,G-A-F-GAP-ACV-OR-16,G-A-F-GAP-ACV-OR,G-A-F-GAP-COCC-OR,G-A-F-GAPCOMM-OR-16,G-A-F-GAPCOMM-OR,G-A-F-GAPCOMPL-OR,G-A-F-GAP-OR-16,G-A-F-GAP-OR,G-A-F-GAPPLUS-OR-16,G-A-F-GAPPLUS-OR,G-A-GAP150-OR-TC,G-A-GAP-CONICELLI-OR,G-A-GAPI-NV-OR,G-A-I-GAP125-OR,G-A-I-GAP-COCC-OR,G-A-I-GAPCOMM-OR,G-A-I-GAP-OR-16,G-A-I-GAP-OR,G-A-I-GAPPLUS-OR}'::text[]))
14. 54.223 104.286 ↑ 1.0 72,550 1

Hash (cost=4,071.50..4,071.50 rows=72,550 width=16) (actual time=104.286..104.286 rows=72,550 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 3401kB
15. 50.063 50.063 ↑ 1.0 72,550 1

Seq Scan on sg_dlr_m1 (cost=0.00..4,071.50 rows=72,550 width=16) (actual time=0.008..50.063 rows=72,550 loops=1)