explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gq8v : Optimization for: plan #hpCR join on mvw_sensors

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.099 186.933 ↑ 100.0 1 1

Result (cost=47,818.43..47,818.94 rows=100 width=32) (actual time=186.932..186.933 rows=1 loops=1)

2.          

CTE elements

3. 0.200 186.790 ↓ 10.0 10 1

Subquery Scan on row (cost=47,818.38..47,818.41 rows=1 width=32) (actual time=186.625..186.790 rows=10 loops=1)

4. 0.004 186.590 ↓ 10.0 10 1

Limit (cost=47,818.38..47,818.39 rows=1 width=828) (actual time=186.584..186.590 rows=10 loops=1)

5. 0.350 186.586 ↓ 10.0 10 1

Sort (cost=47,818.38..47,818.39 rows=1 width=828) (actual time=186.583..186.586 rows=10 loops=1)

  • Sort Key: (count(w.id)) DESC
  • Sort Method: top-N heapsort Memory: 27kB
6. 30.790 186.236 ↓ 246.0 246 1

GroupAggregate (cost=41,412.10..47,818.37 rows=1 width=828) (actual time=155.046..186.236 rows=246 loops=1)

  • Group Key: s.account
7. 4.710 155.446 ↑ 40.1 1,998 1

Sort (cost=41,412.10..41,612.29 rows=80,078 width=619) (actual time=154.759..155.446 rows=1,998 loops=1)

  • Sort Key: s.account
  • Sort Method: quicksort Memory: 481kB
8. 9.094 150.736 ↑ 40.1 1,998 1

Hash Join (cost=220.98..12,717.60 rows=80,078 width=619) (actual time=8.220..150.736 rows=1,998 loops=1)

  • Hash Cond: (s.sensor_id = w.sensor_id)
9. 134.833 134.833 ↑ 1.0 28,857 1

Foreign Scan on mvw_sensors s (cost=100.00..9,703.71 rows=28,857 width=25) (actual time=1.390..134.833 rows=28,857 loops=1)

10. 0.933 6.809 ↓ 3.6 1,998 1

Hash (cost=114.04..114.04 rows=555 width=103) (actual time=6.809..6.809 rows=1,998 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 264kB
11. 0.196 5.876 ↓ 3.6 1,998 1

Append (cost=0.00..114.04 rows=555 width=103) (actual time=0.075..5.876 rows=1,998 loops=1)

12. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on eod w (cost=0.00..0.00 rows=1 width=1,060) (actual time=0.001..0.002 rows=0 loops=1)

  • Filter: ((put_time_utc > '2020-01-23 04:06:53.586086'::timestamp without time zone) AND (create_time_utc >= '2020-01-30 04:05:53.586086'::timestamp without time zone))
13. 0.015 0.015 ↓ 0.0 0 1

Index Scan using i_eod_2020w0120_create_time on eod_2020w0120 w_1 (cost=0.43..4.49 rows=1 width=100) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: (create_time_utc >= '2020-01-30 04:05:53.586086'::timestamp without time zone)
  • Filter: (put_time_utc > '2020-01-23 04:06:53.586086'::timestamp without time zone)
14. 5.663 5.663 ↓ 3.6 1,998 1

Index Scan using i_eod_2020w0127_create_time on eod_2020w0127 w_2 (cost=0.43..109.54 rows=553 width=101) (actual time=0.056..5.663 rows=1,998 loops=1)

  • Index Cond: (create_time_utc >= '2020-01-30 04:05:53.586086'::timestamp without time zone)
  • Filter: (put_time_utc > '2020-01-23 04:06:53.586086'::timestamp without time zone)
15.          

Initplan (for Result)

16. 186.834 186.834 ↓ 10.0 10 1

CTE Scan on elements (cost=0.00..0.03 rows=1 width=32) (actual time=186.634..186.834 rows=10 loops=1)

Planning time : 21.301 ms
Execution time : 187.395 ms