explain.depesz.com

PostgreSQL's explain analyze made readable

Result: V9o41

Settings
# exclusive inclusive rows x rows loops node
1. 6,697.089 100,416.118 ↑ 1.0 1 1

Aggregate (cost=220,486.70..220,486.71 rows=1 width=32) (actual time=100,416.105..100,416.118 rows=1 loops=1)

  • Functions: 37
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 13.069 ms, Inlining 0.000 ms, Optimization 2.496 ms, Emission 92.443 ms, Total 108.008 ms
2. 13,688.959 93,719.029 ↓ 19.6 782,771 1

Hash Join (cost=205,554.03..220,086.67 rows=40,002 width=32) (actual time=29,842.035..93,719.029 rows=782,771 loops=1)

  • Hash Cond: ((""*SELECT* 1"".device)::text = (deviceid_to_userid.device)::text)
3. 12,567.939 79,841.621 ↓ 19.6 782,771 1

Append (cost=205,374.03..219,801.58 rows=40,002 width=39) (actual time=29,653.547..79,841.621 rows=782,771 loops=1)

4. 12,801.361 67,272.946 ↓ 19.6 782,771 1

Subquery Scan on "*SELECT* 1" (cost=205,374.03..219,596.76 rows=40,000 width=39) (actual time=29,653.532..67,272.946 rows=782,771 loops=1)

5. 17,599.197 54,471.585 ↓ 19.6 782,771 1

GroupAggregate (cost=205,374.03..219,196.76 rows=40,000 width=47) (actual time=29,653.515..54,471.585 rows=782,771 loops=1)

  • Group Key: _materialized_hypertable_134.time_bucket, _materialized_hypertable_134.device
6. 16,766.347 36,872.388 ↓ 1.1 782,771 1

Sort (cost=205,374.03..207,234.42 rows=744,156 width=147) (actual time=29,653.388..36,872.388 rows=782,771 loops=1)

  • Sort Key: _materialized_hypertable_134.time_bucket, _materialized_hypertable_134.device
  • Sort Method: external merge Disk: 119,416kB
7. 12,647.043 20,106.041 ↓ 1.1 782,771 1

Custom Scan (ChunkAppend) on _materialized_hypertable_134 (cost=0.68..34,473.10 rows=744,156 width=147) (actual time=0.393..20,106.041 rows=782,771 loops=1)

  • Chunks excluded during startup: 0
8. 7,458.998 7,458.998 ↓ 1.1 782,771 1

Index Scan using _hyper_134_360_chunk__materialized_hypertable_134_time_bucket_i on _hyper_134_360_chunk (cost=0.68..34,473.10 rows=744,156 width=147) (actual time=0.377..7,458.998 rows=782,771 loops=1)

  • Index Cond: ((time_bucket < COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark(1)), '-infinity'::timestamp without time zone)) AND (time_bucket >= '2020-08-28 13:52:20.378423'::timestamp without time zone))
9. 0.018 0.736 ↓ 0.0 0 1

Subquery Scan on "*SELECT* 2" (cost=4.73..4.82 rows=2 width=39) (actual time=0.730..0.736 rows=0 loops=1)

10. 0.017 0.718 ↓ 0.0 0 1

GroupAggregate (cost=4.73..4.80 rows=2 width=47) (actual time=0.711..0.718 rows=0 loops=1)

  • Group Key: (time_bucket('00:05:00'::interval, diag_memory_vm_used.""time"")), diag_memory_vm_used.device
11. 0.026 0.701 ↓ 0.0 0 1

Sort (cost=4.73..4.73 rows=2 width=47) (actual time=0.695..0.701 rows=0 loops=1)

  • Sort Key: (time_bucket('00:05:00'::interval, diag_memory_vm_used.""time"")), diag_memory_vm_used.device
  • Sort Method: quicksort Memory: 25kB
12. 0.019 0.675 ↓ 0.0 0 1

Custom Scan (ChunkAppend) on diag_memory_vm_used (cost=0.68..4.72 rows=2 width=47) (actual time=0.669..0.675 rows=0 loops=1)

  • Chunks excluded during startup: 1
13. 0.656 0.656 ↓ 0.0 0 1

Index Scan using _hyper_1_319_chunk_memory_vm_used_time_idx on _hyper_1_319_chunk (cost=0.68..2.90 rows=1 width=47) (actual time=0.649..0.656 rows=0 loops=1)

  • Index Cond: ((""time"" >= COALESCE(_timescaledb_internal.to_timestamp_without_timezone(_timescaledb_internal.cagg_watermark(1)), '-infinity'::timestamp without time zone)) AND (""time"" >= '2020-08-28 13:52:20.378423'::timestamp without time zone))
  • Filter: (time_bucket('00:05:00'::interval, ""time"") >= '2020-08-28 13:52:20.378423'::timestamp without time zone)
14. 45.448 188.449 ↑ 1.0 5,000 1

Hash (cost=117.50..117.50 rows=5,000 width=7) (actual time=188.442..188.449 rows=5,000 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 255kB
15. 143.001 143.001 ↑ 1.0 5,000 1

Seq Scan on deviceid_to_userid (cost=0.00..117.50 rows=5,000 width=7) (actual time=96.502..143.001 rows=5,000 loops=1)

  • Filter: (userid = 123,456)
Planning time : 3.211 ms
Execution time : 100,462.577 ms