explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dsiz

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 22,090.161 ↑ 1.0 10 1

Limit (cost=92,634.54..96,602.84 rows=10 width=20) (actual time=1,991.749..22,090.161 rows=10 loops=1)

2. 1.999 22,090.151 ↑ 528.5 10 1

Nested Loop (cost=92,634.54..2,189,832.66 rows=5,285 width=20) (actual time=1,991.747..22,090.151 rows=10 loops=1)

3. 17.216 22,087.824 ↑ 485.2 8 1

Bitmap Heap Scan on consumption c (cost=92,634.10..2,165,767.99 rows=3,882 width=16) (actual time=1,991.327..22,087.824 rows=8 loops=1)

  • Recheck Cond: ((todate IS NULL) OR (((status)::text = 'ACTUAL'::text) AND (NOT historical) AND (fromdate <= '2020-08-04'::date) AND (todate > '2020-07-04'::date)))
  • Filter: ((deleted IS FALSE) AND (NOT historical) AND ((sourceid)::text <> 'AMR_AGGREGATION'::text) AND (fromdate <= '2020-08-04'::date) AND ((status)::text = 'ACTUAL'::text) AND (((sourceid)::text <> 'USER'::text) OR ((measurementfrequency)::text <> 'QUARTER_HOURLY'::text)) AND (((sourceid)::text <> 'USER'::text) OR ((measurementfrequency)::text <> 'TEN_MIN'::text)) AND (((sourceid)::text <> 'USER'::text) OR ((measurementfrequency)::text <> 'HALF_HOURLY'::text)) AND (((sourceid)::text <> 'DGO'::text) OR ((measurementfrequency)::text <> 'QUARTER_HOURLY'::text)) AND (((sourceid)::text <> 'DGO'::text) OR ((measurementfrequency)::text <> 'TEN_MIN'::text)) AND (((sourceid)::text <> 'DGO'::text) OR ((measurementfrequency)::text <> 'HALF_HOURLY'::text)) AND (SubPlan 1))
  • Rows Removed by Filter: 823
  • Heap Blocks: exact=685
4. 0.001 460.453 ↓ 0.0 0 1

BitmapOr (cost=92,634.10..92,634.10 rows=8,295 width=0) (actual time=460.453..460.453 rows=0 loops=1)

5. 0.007 0.007 ↓ 0.0 0 1

Bitmap Index Scan on consumption_todate2_idx (cost=0.00..1.44 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (todate IS NULL)
6. 460.445 460.445 ↓ 1.2 9,735 1

Bitmap Index Scan on consumption_deliverypointid_status_historical_release_fromd_idx (cost=0.00..92,630.72 rows=8,295 width=0) (actual time=460.445..460.445 rows=9,735 loops=1)

  • Index Cond: (((status)::text = 'ACTUAL'::text) AND (historical = false) AND (fromdate <= '2020-08-04'::date) AND (todate > '2020-07-04'::date))
7.          

SubPlan (for Bitmap Heap Scan)

8. 835.828 21,610.155 ↓ 0.0 0 831

Nested Loop (cost=1.27..496.56 rows=1 width=4) (actual time=26.005..26.005 rows=0 loops=831)

9. 560.415 4,079.379 ↓ 67.9 10,045 831

Nested Loop (cost=0.85..422.60 rows=148 width=4) (actual time=0.012..4.909 rows=10,045 loops=831)

10. 9.972 9.972 ↑ 7.5 11 831

Index Scan using idx_crmdata_profilecodeid on crmdata crm (cost=0.42..67.63 rows=82 width=4) (actual time=0.005..0.012 rows=11 loops=831)

  • Index Cond: (profilecodeid = 60,036)
11. 3,508.992 3,508.992 ↓ 304.3 913 9,138

Index Scan using idx_contract_customerid_pk on contract co (cost=0.42..4.30 rows=3 width=8) (actual time=0.005..0.384 rows=913 loops=9,138)

  • Index Cond: (customerid = crm.customerid)
12. 16,694.948 16,694.948 ↓ 0.0 0 8,347,474

Index Scan using contractdelively_contract_id_idx on contractdelivery (cost=0.42..0.49 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=8,347,474)

  • Index Cond: (contractid = co.contractid)
  • Filter: (((c.externalref IS NULL) AND (fromdate < c.todate) AND ((todate IS NULL) OR (todate > c.fromdate))) OR ((c.externalref IS NOT NULL) AND ((c.externalref)::text = ((contractid)::character varying)::text)))
  • Rows Removed by Filter: 1
13. 0.328 0.328 ↑ 10.0 1 8

Index Scan using "IX_consumptiondetail_consumptionid_consumptiondetail" on consumptiondetail (cost=0.43..5.42 rows=10 width=8) (actual time=0.038..0.041 rows=1 loops=8)

  • Index Cond: (consumptionid = c.consumptionid)
  • Filter: ((ignored IS FALSE) AND ((direction)::text = 'CONSUMPTION'::text) AND ((measurementnature)::text <> ALL ('{REACTIVE_CAPACITIVE_ENERGY,REACTIVE_CAPACITIVE_POWER,REACTIVE_INDUCTIVE_ENERGY,REACTIVE_INDUCTIVE_POWER}'::text[])))
Planning time : 3.410 ms
Execution time : 22,090.298 ms