explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GcJT

Settings
# exclusive inclusive rows x rows loops node
1. 0.229 394.909 ↑ 306.4 153 1

Sort (cost=70,813.72..70,930.91 rows=46,875 width=742) (actual time=394.902..394.909 rows=153 loops=1)

  • Sort Key: (sum(maindata.ranking)) DESC, maindata.device_name, maindata.device_id
  • Sort Method: quicksort Memory: 46kB
2.          

CTE maindata

3. 113.692 359.853 ↑ 4.4 21,312 1

GroupAggregate (cost=36,433.73..44,871.23 rows=93,750 width=466) (actual time=201.266..359.853 rows=21,312 loops=1)

  • Group Key: o.device_id, devices.name, devices.category, r.id
  • Filter: ((sum((((r.factor * (o.ranking)::double precision) * (date_part('epoch'::text, CASE WHEN ('2020-01-01 00:00:00'::timestamp without time zone < o.triggered_until) THEN '2020-01-01 00:00:00'::timestamp without time zone ELSE o.triggered_unt (...)
4. 151.019 246.161 ↓ 1.0 96,180 1

Sort (cost=36,433.73..36,668.10 rows=93,750 width=486) (actual time=201.237..246.161 rows=96,180 loops=1)

  • Sort Key: o.device_id, devices.name, devices.category, r.id
  • Sort Method: external merge Disk: 9240kB
5. 38.549 95.142 ↓ 1.0 96,180 1

Hash Join (cost=1,089.21..8,181.60 rows=93,750 width=486) (actual time=9.239..95.142 rows=96,180 loops=1)

  • Hash Cond: (o.device_id = devices.id)
6. 21.264 47.467 ↑ 1.0 96,180 1

Hash Join (cost=11.30..5,802.79 rows=96,902 width=454) (actual time=0.040..47.467 rows=96,180 loops=1)

  • Hash Cond: (o.rule_id = r.id)
7. 26.189 26.189 ↑ 2.0 96,180 1

Seq Scan on device_ranking_occurrences o (cost=0.00..4,095.71 rows=193,803 width=28) (actual time=0.018..26.189 rows=96,180 loops=1)

  • Filter: (triggered_until <> triggered_at)
  • Rows Removed by Filter: 98579
8. 0.004 0.014 ↑ 8.0 5 1

Hash (cost=10.80..10.80 rows=40 width=430) (actual time=0.014..0.014 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.010 0.010 ↑ 8.0 5 1

Seq Scan on device_ranking_rules r (cost=0.00..10.80 rows=40 width=430) (actual time=0.009..0.010 rows=5 loops=1)

  • Filter: is_visible
10. 3.301 9.126 ↑ 1.0 14,102 1

Hash (cost=901.64..901.64 rows=14,102 width=36) (actual time=9.126..9.126 rows=14,102 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1115kB
11. 5.825 5.825 ↑ 1.0 14,102 1

Seq Scan on devices (cost=0.00..901.64 rows=14,102 width=36) (actual time=0.013..5.825 rows=14,102 loops=1)

  • Filter: ((activation_status < 9999) AND (company_id = 100))
  • Rows Removed by Filter: 474
12. 1.665 394.680 ↑ 306.4 153 1

Hash Join (cost=3,290.49..5,985.81 rows=46,875 width=742) (actual time=391.280..394.680 rows=153 loops=1)

  • Hash Cond: (p.device_id = maindata.device_id)
13. 203.146 203.146 ↑ 4.4 21,312 1

CTE Scan on maindata p (cost=0.00..1,875.00 rows=93,750 width=434) (actual time=201.269..203.146 rows=21,312 loops=1)

14. 0.027 189.869 ↑ 1.0 100 1

Hash (cost=3,289.24..3,289.24 rows=100 width=312) (actual time=189.869..189.869 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
15. 0.006 189.842 ↑ 1.0 100 1

Limit (cost=3,287.99..3,288.24 rows=100 width=312) (actual time=189.831..189.842 rows=100 loops=1)

16. 3.623 189.836 ↑ 93.8 100 1

Sort (cost=3,287.99..3,311.43 rows=9,375 width=312) (actual time=189.830..189.836 rows=100 loops=1)

  • Sort Key: (sum(maindata.ranking)) DESC, maindata.device_name, maindata.device_id
  • Sort Method: top-N heapsort Memory: 32kB
17. 19.016 186.213 ↓ 1.5 13,947 1

HashAggregate (cost=2,812.50..2,929.69 rows=9,375 width=312) (actual time=182.345..186.213 rows=13,947 loops=1)

  • Group Key: maindata.device_id, maindata.device_name, maindata.category
18. 167.197 167.197 ↑ 4.4 21,312 1

CTE Scan on maindata (cost=0.00..1,875.00 rows=93,750 width=288) (actual time=0.001..167.197 rows=21,312 loops=1)

Planning time : 1.122 ms
Execution time : 399.097 ms