explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xRqr

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.034 ↓ 0.0 0 1

GroupAggregate (cost=122.97..123.07 rows=1 width=702) (actual time=0.034..0.034 rows=0 loops=1)

  • Group Key: cd.subscriptionnumber, cd.servicecode, cd.networkcode
2. 0.025 0.034 ↓ 0.0 0 1

Sort (cost=122.97..122.98 rows=1 width=486) (actual time=0.034..0.034 rows=0 loops=1)

  • Sort Key: cd.subscriptionnumber, cd.servicecode, cd.networkcode
  • Sort Method: quicksort Memory: 25kB
3. 0.000 0.009 ↓ 0.0 0 1

Hash Join (cost=72.29..122.96 rows=1 width=486) (actual time=0.009..0.009 rows=0 loops=1)

  • Hash Cond: ((cd.servicecode = tax.servicecode) AND (cd.companynumber = tax.companynumber))
  • Join Filter: (((CASE WHEN (cocxrep.cxa8dt = '0'::numeric) THEN NULL::date WHEN (cocxrep.cxa8dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 7, 2)))::date END IS NULL) AND (agr.edsjst = '0'::bpchar) AND (CASE WHEN (agr.eda2dt = '0'::numeric) THEN NULL::date WHEN (agr.eda2dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + agr.eda2dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + agr.eda2dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + agr.eda2dt))::character varying)::text, 7, 2)))::date END > '2018-07-05'::date) AND ((datetimediff('DAYS'::character varying, '2018-07-05 00:00:00'::timestamp without time zone, (CASE WHEN (cocxrep.cxa3dt = '0'::numeric) THEN NULL::date WHEN (cocxrep.cxa3dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + cocxrep.cxa3dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa3dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa3dt))::character varying)::text, 7, 2)))::date END)::timestamp without time zone))::numeric >= codxrep.dxcknb)) OR ((CASE WHEN (cocxrep.cxbddt = '0'::numeric) THEN NULL::date WHEN (cocxrep.cxbddt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + cocxrep.cxbddt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxbddt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxbddt))::character varying)::text, 7, 2)))::date END IS NOT NULL) AND (cocxrep.cxgist = '0'::bpchar) AND (CASE WHEN (agr.eda2dt = '0'::numeric) THEN NULL::date WHEN (agr.eda2dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + agr.eda2dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + agr.eda2dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + agr.eda2dt))::character varying)::text, 7, 2)))::date END >= '2018-07-05'::date) AND ((datetimediff('DAYS'::character varying, '2018-07-05 00:00:00'::timestamp without time zone, (CASE WHEN (cocxrep.cxbddt = '0'::numeric) THEN NULL::date WHEN (cocxrep.cxbddt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + cocxrep.cxbddt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxbddt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxbddt))::character varying)::text, 7, 2)))::date END)::timestamp without time zone))::numeric >= codxrep.dxclnb) AND (((datetimediff('DAYS'::character varying, '2018-07-05 00:00:00'::timestamp without time zone, (CASE WHEN (cocxrep.cxa9dt = '0'::numeric) THEN NULL::date WHEN (cocxrep.cxa9dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + cocxrep.cxa9dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa9dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa9dt))::character varying)::text, 7, 2)))::date END)::timestamp without time zone))::numeric >= codxrep.dxfvqt) OR (CASE WHEN (cocxrep.cxa9dt = '0'::numeric) THEN NULL::date WHEN (cocxrep.cxa9dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + cocxrep.cxa9dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa9dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa9dt))::character varying)::text, 7, 2)))::date END IS NULL)) AND (agr.edskst = '0'::bpchar)) OR ((CASE WHEN (agr.eda2dt = '0'::numeric) THEN NULL::date WHEN (agr.eda2dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + agr.eda2dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + agr.eda2dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + agr.eda2dt))::character varying)::text, 7, 2)))::date END <= '2018-07-05'::date) AND ((CASE WHEN (cocxrep.cxa8dt = '0'::numeric) THEN NULL::date WHEN (cocxrep.cxa8dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 7, 2)))::date END IS NULL) OR (CASE WHEN (cocxrep.cxa8dt = '0'::numeric) THEN NULL::date WHEN (cocxrep.cxa8dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 7, 2)))::date END <> CASE WHEN (cocxrep.cxa9dt = '0'::numeric) THEN NULL::date WHEN (cocxrep.cxa9dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + cocxrep.cxa9dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa9dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa9dt))::character varying)::text, 7, 2)))::date END) OR ((CASE WHEN (cocxrep.cxa8dt = '0'::numeric) THEN NULL::date WHEN (cocxrep.cxa8dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 7, 2)))::date END IS NOT NULL) AND ((datetimediff('DAYS'::character varying, '2018-07-05 00:00:00'::timestamp without time zone, (CASE WHEN (cocxrep.cxa8dt = '0'::numeric) THEN NULL::date WHEN (cocxrep.cxa8dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + cocxrep.cxa8dt))::character varying)::text, 7, 2)))::date END)::timestamp without time zone))::numeric >= codxrep.dxfuqt))) AND (cd.usagestatus = '3'::bpchar) AND (tax.servicecategory = 'A'::bpchar) AND (tax.issuppressfrominvoice = '0'::bpchar) AND (cd.usagedatetime < CURRENT_DATE)))
4. 0.000 0.009 ↓ 0.0 0 1

Nested Loop (cost=0.55..49.37 rows=11 width=377) (actual time=0.009..0.009 rows=0 loops=1)

5. 0.000 0.009 ↓ 0.0 0 1

Nested Loop (cost=0.55..48.89 rows=11 width=340) (actual time=0.009..0.009 rows=0 loops=1)

6. 0.001 0.009 ↓ 0.0 0 1

Nested Loop (cost=0.28..39.05 rows=20 width=334) (actual time=0.009..0.009 rows=0 loops=1)

7. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on ratedusage cd (cost=0.00..10.20 rows=20 width=302) (actual time=0.008..0.008 rows=0 loops=1)

8. 0.000 0.000 ↓ 0.0 0

Index Scan using xpksubscription on cocxrep (cost=0.28..1.44 rows=1 width=37) (never executed)

  • Index Cond: (cxb8nb = cd.subscriptionnumber)
9. 0.000 0.000 ↓ 0.0 0

Index Scan using xpkagreement on coedrep agr (cost=0.28..0.49 rows=1 width=77) (never executed)

  • Index Cond: (edb6nb = cocxrep.cxb6nb)
  • Filter: (((edsjst = '0'::bpchar) AND (CASE WHEN (eda2dt = '0'::numeric) THEN NULL::date WHEN (eda2dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + eda2dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + eda2dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + eda2dt))::character varying)::text, 7, 2)))::date END > '2018-07-05'::date)) OR ((CASE WHEN (eda2dt = '0'::numeric) THEN NULL::date WHEN (eda2dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + eda2dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + eda2dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + eda2dt))::character varying)::text, 7, 2)))::date END >= '2018-07-05'::date) AND (edskst = '0'::bpchar)) OR (CASE WHEN (eda2dt = '0'::numeric) THEN NULL::date WHEN (eda2dt = '9999999'::numeric) THEN '9999-12-31'::date ELSE (((((substr(((('19000000'::numeric + eda2dt))::character varying)::text, 1, 4) || '-'::text) || substr(((('19000000'::numeric + eda2dt))::character varying)::text, 5, 2)) || '-'::text) || substr(((('19000000'::numeric + eda2dt))::character varying)::text, 7, 2)))::date END <= '2018-07-05'::date))
10. 0.000 0.000 ↓ 0.0 0

Index Scan using xif7tariff on codxrep (cost=0.00..0.03 rows=1 width=92) (never executed)

  • Index Cond: (dxcicd = cocxrep.cxcicd)
  • Filter: (cocxrep.cxb6cd = dxb6cd)
11. 0.000 0.000 ↓ 0.0 0

Hash (cost=71.72..71.72 rows=1 width=225) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Subquery Scan on tax (cost=66.79..71.72 rows=1 width=225) (never executed)

  • Filter: (tax.rownum = 1)
13. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=66.79..69.96 rows=141 width=237) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Sort (cost=66.79..67.14 rows=141 width=229) (never executed)

  • 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
15. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=43.99..61.75 rows=141 width=229) (never executed)

  • Hash Cond: ((codyrep.dycicd = cwawrep.awcicd) AND (codyrep.dybucd = cwawrep.awbucd))
16. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=33.64..50.64 rows=144 width=119) (never executed)

  • Hash Cond: ((coeirep.eicicd = codyrep.dycicd) AND (coeirep.eibucd = codyrep.dybucd))
17. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=5.19..19.98 rows=144 width=889) (never executed)

  • Hash Cond: ((coeirep.eicicd = cfa9rep.a9cicd) AND (coeirep.eibucd = cfa9rep.a9bucd) AND (coeirep.eibfdt = cfa9rep.a9bfdt))
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on coeirep (cost=0.00..6.44 rows=144 width=32) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Hash (cost=3.16..3.16 rows=116 width=25) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Seq Scan on cfa9rep (cost=0.00..3.16 rows=116 width=25) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Hash (cost=25.78..25.78 rows=178 width=83) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Seq Scan on codyrep (cost=0.00..25.78 rows=178 width=83) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Hash (cost=7.74..7.74 rows=174 width=132) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Seq Scan on cwawrep (cost=0.00..7.74 rows=174 width=132) (never executed)