explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xTe9

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 679.925 ↑ 1.0 10 1

Limit (cost=21,956.46..21,956.48 rows=10 width=470) (actual time=679.921..679.925 rows=10 loops=1)

2. 2.717 679.920 ↑ 3,830.4 10 1

Sort (cost=21,956.46..22,052.22 rows=38,304 width=470) (actual time=679.919..679.920 rows=10 loops=1)

  • Sort Key: ((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 (...)
  • Sort Method: top-N heapsort Memory: 26kB
3. 192.835 677.203 ↑ 4.2 9,225 1

GroupAggregate (cost=18,734.72..21,128.72 rows=38,304 width=470) (actual time=329.935..677.203 rows=9,225 loops=1)

  • Group Key: o.device_id, o.rule_id, devices.name, r.name, devices.category, r.priority
4. 345.385 484.368 ↓ 4.2 160,031 1

Sort (cost=18,734.72..18,830.48 rows=38,304 width=490) (actual time=329.868..484.368 rows=160,031 loops=1)

  • Sort Key: o.device_id, o.rule_id, devices.name, r.name, devices.category, r.priority
  • Sort Method: external merge Disk: 15312kB
5. 58.227 138.983 ↓ 4.2 160,031 1

Hash Join (cost=1,090.22..7,306.79 rows=38,304 width=490) (actual time=6.483..138.983 rows=160,031 loops=1)

  • Hash Cond: (o.device_id = devices.id)
6. 33.961 74.402 ↓ 4.0 160,031 1

Hash Join (cost=12.30..5,697.36 rows=39,592 width=458) (actual time=0.054..74.402 rows=160,031 loops=1)

  • Hash Cond: (o.rule_id = r.id)
7. 40.423 40.431 ↓ 2.0 160,031 1

Seq Scan on device_ranking_occurrences o (cost=1.00..4,993.20 rows=79,184 width=28) (actual time=0.041..40.431 rows=160,031 loops=1)

  • Filter: ((triggered_until <> triggered_at) AND (NOT (hashed SubPlan 1)) AND (triggered_until >= '2019-01-01 00:00:00'::timestamp without time zone) AND (triggered_at <= '2020-01-01 00:00:00'::timestamp without time zon (...)
  • Rows Removed by Filter: 23
8.          

SubPlan (forSeq Scan)

9. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on device_ranking_invisibility (cost=0.00..1.00 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: (user_id = 101)
10. 0.002 0.010 ↑ 10.0 4 1

Hash (cost=10.80..10.80 rows=40 width=434) (actual time=0.010..0.010 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.008 0.008 ↑ 10.0 4 1

Seq Scan on device_ranking_rules r (cost=0.00..10.80 rows=40 width=434) (actual time=0.007..0.008 rows=4 loops=1)

  • Filter: is_visible
12. 2.370 6.354 ↑ 1.0 14,102 1

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

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

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

  • Filter: ((activation_status < 9999) AND (company_id = 100))
  • Rows Removed by Filter: 474
Planning time : 0.918 ms
Execution time : 683.437 ms