explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H8grP

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 19,395.361 ↑ 1.0 10 1

Limit (cost=92,634.54..96,602.84 rows=10 width=20) (actual time=1,950.011..19,395.361 rows=10 loops=1)

2. 2.040 19,395.353 ↑ 528.5 10 1

Nested Loop (cost=92,634.54..2,189,832.66 rows=5,285 width=20) (actual time=1,950.010..19,395.353 rows=10 loops=1)

3. 13.339 19,392.993 ↑ 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,949.593..19,392.993 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.002 431.192 ↓ 0.0 0 1

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

5. 0.006 0.006 ↓ 0.0 0 1

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

  • Index Cond: (todate IS NULL)
6. 431.184 431.184 ↓ 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=431.184..431.184 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. 0.000 18,948.462 ↓ 0.0 0 831

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

9. 571.029 3,512.637 ↓ 67.9 10,045 831

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

10. 8.310 8.310 ↑ 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.003..0.010 rows=11 loops=831)

  • Index Cond: (profilecodeid = 60,036)
11. 2,933.298 2,933.298 ↓ 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.004..0.321 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: (((todate IS NULL) OR (c.fromdate < todate)) AND (c.todate > fromdate) AND ((c.externalref IS NULL) OR ((c.externalref)::integer = contractid)))
  • Rows Removed by Filter: 1
13. 0.320 0.320 ↑ 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.040 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.117 ms
Execution time : 19,395.493 ms