explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B6qV

Settings
# exclusive inclusive rows x rows loops node
1. 20.863 5,121.835 ↓ 2.5 20,487 1

Hash Join (cost=4,141,836.23..4,361,107.08 rows=8,070 width=131) (actual time=5,025.781..5,121.835 rows=20,487 loops=1)

  • Hash Cond: (stm_device_stats_meters_log.device_id = gdm_devices.device_id)
2. 13.342 5,099.896 ↑ 1.6 20,487 1

Hash Join (cost=4,141,776.53..4,360,803.82 rows=32,669 width=124) (actual time=5,024.692..5,099.896 rows=20,487 loops=1)

  • Hash Cond: (stm_device_stats_meters_log.meter_id = stm.meter_id)
3. 50.908 5,085.234 ↑ 6.7 24,393 1

GroupAggregate (cost=4,141,730.52..4,358,170.09 rows=164,439 width=94) (actual time=5,023.354..5,085.234 rows=24,393 loops=1)

  • Group Key: stm_device_stats_meters_log.device_id, stm_device_stats_meters_log.meter_id
4. 136.295 5,034.326 ↑ 264.7 46,365 1

Sort (cost=4,141,730.52..4,172,415.55 rows=12,274,010 width=26) (actual time=5,023.341..5,034.326 rows=46,365 loops=1)

  • Sort Key: stm_device_stats_meters_log.device_id, stm_device_stats_meters_log.meter_id
  • Sort Method: external merge Disk: 1824kB
5. 11.616 4,898.031 ↑ 264.7 46,365 1

Nested Loop (cost=2,103,103.71..2,109,185.87 rows=12,274,010 width=26) (actual time=4,864.517..4,898.031 rows=46,365 loops=1)

6. 0.512 4,864.638 ↓ 1.5 427 1

HashAggregate (cost=2,103,103.14..2,103,106.08 rows=294 width=24) (actual time=4,864.490..4,864.638 rows=427 loops=1)

  • Group Key: a.device_id, (min(a.meter_date))
7. 0.136 4,864.126 ↓ 1.5 432 1

Append (cost=0.84..2,103,101.67 rows=294 width=24) (actual time=29.142..4,864.126 rows=432 loops=1)

8. 12.137 2,456.980 ↓ 1.5 216 1

GroupAggregate (cost=0.84..1,051,549.37 rows=147 width=17) (actual time=29.142..2,456.980 rows=216 loops=1)

  • Group Key: a.device_id
9. 2,443.637 2,444.843 ↓ 214.4 31,515 1

Nested Loop (cost=0.84..1,051,547.16 rows=147 width=17) (actual time=10.499..2,444.843 rows=31,515 loops=1)

  • -> Index Only Scan using stm_device_stats_meters_log_pk on stm_device_stats_meters_log a (cost=0.56..2995.35 rows=1 width=17) (actual time=3.461..5.13
10. 1.206 1.206 ↓ 1.4 475 1

Index Scan using gdm_devices_pk on gdm_devices d (cost=0.28..177.45 rows=351 width=8) (actual time=0.431..1.206 rows=475 loops=1)

  • Filter: (((license_identification)::text <> ''::text) AND (device_model_id = '5'::numeric))
  • Rows Removed by Filter: 946
  • Index Cond: ((device_id = d.device_id) AND (meter_id = '1000'::numeric))
  • Filter: ((date_trunc('DAY'::text, meter_date) >= date_trunc('DAY'::text, ((('now'::cstring)::date - 15))::timestamp with time zone)) AND (date_tru
  • Rows Removed by Filter: 147
  • Heap Fetches: 0
11. 11.860 2,407.010 ↓ 1.5 216 1

GroupAggregate (cost=0.84..1,051,549.37 rows=147 width=17) (actual time=20.496..2,407.010 rows=216 loops=1)

  • Group Key: a_1.device_id
12. 2,394.090 2,395.150 ↓ 214.4 31,515 1

Nested Loop (cost=0.84..1,051,547.16 rows=147 width=17) (actual time=7.397..2,395.150 rows=31,515 loops=1)

  • -> Index Only Scan using stm_device_stats_meters_log_pk on stm_device_stats_meters_log a_1 (cost=0.56..2995.35 rows=1 width=17) (actual time=3.396..5.
13. 1.060 1.060 ↓ 1.4 475 1

Index Scan using gdm_devices_pk on gdm_devices d_1 (cost=0.28..177.45 rows=351 width=8) (actual time=0.330..1.060 rows=475 loops=1)

  • Filter: (((license_identification)::text <> ''::text) AND (device_model_id = '5'::numeric))
  • Rows Removed by Filter: 946
  • Index Cond: ((device_id = d_1.device_id) AND (meter_id = '1000'::numeric))
  • Filter: ((date_trunc('DAY'::text, meter_date) >= date_trunc('DAY'::text, ((('now'::cstring)::date - 15))::timestamp with time zone)) AND (date_tru
  • Rows Removed by Filter: 147
  • Heap Fetches: 0
14. 21.777 21.777 ↓ 27.2 109 427

Index Scan using stm_device_stats_meters_log_pk on stm_device_stats_meters_log (cost=0.56..20.63 rows=4 width=26) (actual time=0.017..0.051 rows=109 loops=427)

  • Index Cond: ((device_id = a.device_id) AND (meter_date = (min(a.meter_date))))
15. 0.146 1.320 ↓ 1.0 361 1

Hash (cost=41.52..41.52 rows=359 width=30) (actual time=1.320..1.320 rows=361 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
16. 1.046 1.174 ↓ 1.0 361 1

Bitmap Heap Scan on stm_meters stm (cost=12.00..41.52 rows=359 width=30) (actual time=0.150..1.174 rows=361 loops=1)

  • Recheck Cond: ((meter_id >= '999'::numeric) AND (meter_id <= '1380'::numeric))
  • Filter: (meter_id <> ALL ('{1059,1075,1091,1107,1123,1139,1155,1171,1187,1203,1219,1235,1251,1267,1283,1299,1315,1331,1347,1363}'::numeric[]))
  • Rows Removed by Filter: 20
  • Heap Blocks: exact=9
17. 0.128 0.128 ↓ 1.0 381 1

Bitmap Index Scan on stm_meters_pk (cost=0.00..11.91 rows=363 width=0) (actual time=0.128..0.128 rows=381 loops=1)

  • Index Cond: ((meter_id >= '999'::numeric) AND (meter_id <= '1380'::numeric))
18. 0.233 1.076 ↓ 1.4 475 1

Hash (cost=55.31..55.31 rows=351 width=13) (actual time=1.076..1.076 rows=475 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
19. 0.843 0.843 ↓ 1.4 475 1

Seq Scan on gdm_devices (cost=0.00..55.31 rows=351 width=13) (actual time=0.016..0.843 rows=475 loops=1)

  • Filter: (((license_identification)::text <> ''::text) AND (device_model_id = '5'::numeric))
  • Rows Removed by Filter: 946
Planning time : 2.475 ms
Execution time : 5,124.699 ms