explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XSsJ

Settings
# exclusive inclusive rows x rows loops node
1. 2.183 17,067.553 ↑ 1.3 3 1

GroupAggregate (cost=105.68..5,589.53 rows=4 width=677) (actual time=17,059.151..17,067.553 rows=3 loops=1)

  • Group Key: cd.subscriptionnumber, cd.servicecode
2. 3.639 17,065.370 ↓ 451.8 1,807 1

Nested Loop (cost=105.68..5,589.18 rows=4 width=305) (actual time=17,053.821..17,065.370 rows=1,807 loops=1)

  • Join Filter: ((cd.servicecode = tax.servicecode) AND (cd.companynumber = tax.companynumber))
  • Rows Removed by Join Filter: 32526
3. 0.022 1.137 ↓ 19.0 19 1

Subquery Scan on tax (cost=52.74..58.38 rows=1 width=223) (actual time=0.924..1.137 rows=19 loops=1)

  • Filter: ((tax.rownum = 1) AND (tax.servicecategory = 'A'::bpchar) AND (tax.issuppressfrominvoice = '0'::bpchar))
  • Rows Removed by Filter: 121
4. 0.195 1.115 ↑ 1.0 140 1

WindowAgg (cost=52.74..55.91 rows=141 width=237) (actual time=0.897..1.115 rows=140 loops=1)

5. 0.236 0.920 ↑ 1.0 140 1

Sort (cost=52.74..53.09 rows=141 width=229) (actual time=0.885..0.920 rows=140 loops=1)

  • Sort Key: coeirep.eibucd, coeirep.eicicd, (CASE WHEN (coeirep.eibfdt = '0'::numeric) THEN NULL::date WHEN (coeirep.eibfdt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + coeirep.eibfdt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + coeirep.eibfdt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + coeirep.eibfdt))::character varying)::text, 7, 2)))::date END) DESC
  • Sort Method: quicksort Memory: 62kB
6. 0.049 0.684 ↑ 1.0 140 1

Hash Join (cost=29.94..47.70 rows=141 width=229) (actual time=0.354..0.684 rows=140 loops=1)

  • Hash Cond: ((codyrep.dycicd = cwawrep.awcicd) AND (codyrep.dybucd = cwawrep.awbucd))
7. 0.050 0.536 ↑ 1.0 144 1

Hash Join (cost=19.62..36.61 rows=144 width=119) (actual time=0.244..0.536 rows=144 loops=1)

  • Hash Cond: ((coeirep.eicicd = codyrep.dycicd) AND (coeirep.eibucd = codyrep.dybucd))
8. 0.259 0.340 ↑ 1.0 144 1

Hash Left Join (cost=5.19..19.98 rows=144 width=889) (actual time=0.090..0.340 rows=144 loops=1)

  • Hash Cond: ((coeirep.eicicd = cfa9rep.a9cicd) AND (coeirep.eibucd = cfa9rep.a9bucd) AND (coeirep.eibfdt = cfa9rep.a9bfdt))
9. 0.015 0.015 ↑ 1.0 144 1

Seq Scan on coeirep (cost=0.00..6.44 rows=144 width=32) (actual time=0.004..0.015 rows=144 loops=1)

10. 0.027 0.066 ↑ 1.0 116 1

Hash (cost=3.16..3.16 rows=116 width=25) (actual time=0.066..0.066 rows=116 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
11. 0.039 0.039 ↑ 1.0 116 1

Seq Scan on cfa9rep (cost=0.00..3.16 rows=116 width=25) (actual time=0.007..0.039 rows=116 loops=1)

12. 0.041 0.146 ↑ 1.0 177 1

Hash (cost=11.77..11.77 rows=177 width=83) (actual time=0.145..0.146 rows=177 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
13. 0.105 0.105 ↑ 1.0 177 1

Seq Scan on codyrep (cost=0.00..11.77 rows=177 width=83) (actual time=0.009..0.105 rows=177 loops=1)

14. 0.036 0.099 ↑ 1.0 173 1

Hash (cost=7.73..7.73 rows=173 width=132) (actual time=0.099..0.099 rows=173 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
15. 0.063 0.063 ↑ 1.0 173 1

Seq Scan on cwawrep (cost=0.00..7.73 rows=173 width=132) (actual time=0.009..0.063 rows=173 loops=1)

16. 16,819.940 17,060.594 ↓ 3.3 1,807 19

Bitmap Heap Scan on ratedusage cd (cost=52.94..5,522.51 rows=553 width=97) (actual time=18.445..897.926 rows=1,807 loops=19)

  • Recheck Cond: (subscriptionnumber = '20090'::numeric)
  • Filter: ((usagestatus = '3'::bpchar) AND (date(usagedatetime) < CURRENT_DATE))
  • Heap Blocks: exact=34333
17. 240.654 240.654 ↑ 1.0 1,807 19

Bitmap Index Scan on xif5ratedusage (cost=0.00..52.80 rows=1,840 width=0) (actual time=12.666..12.666 rows=1,807 loops=19)

  • Index Cond: (subscriptionnumber = '20090'::numeric)
Planning time : 1.576 ms
Execution time : 17,068.287 ms