explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o1P

Settings
# exclusive inclusive rows x rows loops node
1. 2.282 38,274.767 ↑ 41.0 3 1

GroupAggregate (cost=95.68..5,620.94 rows=123 width=677) (actual time=38,266.604..38,274.767 rows=3 loops=1)

  • Group Key: cd.subscriptionnumber, cd.servicecode
2. 3.946 38,272.485 ↓ 14.3 1,762 1

Nested Loop (cost=95.68..5,610.18 rows=123 width=305) (actual time=38,261.352..38,272.485 rows=1,762 loops=1)

  • Join Filter: ((cd.servicecode = tax.servicecode) AND (cd.companynumber = tax.companynumber))
  • Rows Removed by Join Filter: 31716
3. 0.010 77.893 ↓ 19.0 19 1

Subquery Scan on tax (cost=39.44..39.86 rows=1 width=223) (actual time=77.820..77.893 rows=19 loops=1)

  • Filter: (tax.rownum = 1)
4. 0.104 77.883 ↓ 1.6 19 1

WindowAgg (cost=39.44..39.71 rows=12 width=243) (actual time=77.819..77.883 rows=19 loops=1)

5. 0.145 77.779 ↓ 1.6 19 1

Sort (cost=39.44..39.47 rows=12 width=227) (actual time=77.772..77.779 rows=19 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: 30kB
6. 0.032 77.634 ↓ 1.6 19 1

Nested Loop (cost=18.21..39.23 rows=12 width=227) (actual time=77.286..77.634 rows=19 loops=1)

  • Join Filter: ((codyrep.dycicd = cwawrep.awcicd) AND (codyrep.dybucd = cwawrep.awbucd))
7. 0.084 0.804 ↓ 1.6 19 1

Hash Join (cost=18.07..35.07 rows=12 width=117) (actual time=0.516..0.804 rows=19 loops=1)

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

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

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

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

10. 0.065 0.122 ↑ 1.0 116 1

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

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

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

12. 0.019 0.206 ↓ 1.3 19 1

Hash (cost=12.65..12.65 rows=15 width=81) (actual time=0.206..0.206 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
13. 0.187 0.187 ↓ 1.3 19 1

Seq Scan on codyrep (cost=0.00..12.65 rows=15 width=81) (actual time=0.109..0.187 rows=19 loops=1)

  • Filter: ((dyewna = 'A'::bpchar) AND (dyecsv = '0'::bpchar))
  • Rows Removed by Filter: 158
14. 76.798 76.798 ↑ 1.0 1 19

Index Scan using xpkservice_extension on cwawrep (cost=0.14..0.33 rows=1 width=132) (actual time=4.042..4.042 rows=1 loops=19)

  • Index Cond: ((awcicd = coeirep.eicicd) AND (awbucd = coeirep.eibucd))
15. 38,107.559 38,190.646 ↓ 1.2 1,762 19

Bitmap Heap Scan on ratedusage cd (cost=56.24..5,548.16 rows=1,477 width=97) (actual time=4.702..2,010.034 rows=1,762 loops=19)

  • Recheck Cond: (subscriptionnumber = '20300'::numeric)
  • Filter: ((usagestatus = '3'::bpchar) AND (usagedatetime < CURRENT_DATE))
  • Heap Blocks: exact=33478
16. 83.087 83.087 ↑ 1.0 1,762 19

Bitmap Index Scan on xif5ratedusage (cost=0.00..55.87 rows=1,849 width=0) (actual time=4.373..4.373 rows=1,762 loops=19)

  • Index Cond: (subscriptionnumber = '20300'::numeric)