explain.depesz.com

A tool for finding a real cause for slow queries.

Result: fAS

options
exclusive inclusive rows x rows loops node
0.000 0.000 ↓ 0.0
→

Unique (cost=291278162.48..292106809.52 rows=55243136 width=32) (actual time=.. rows= loops=)

  • Output: (first_value(raw.file_id) OVER (?)), raw.series_id, (first_value(raw."row") OVER (?)), times."time", (first_value(raw.value) OVER (?)), cr1000tables.order2, files.size, raw.file_id, raw."row
  • Total runtime: 35296283.316 ms
0.000 0.000 ↓ 0.0
→

Sort (cost=291278162.48..291416270.32 rows=55243136 width=32) (actual time=.. rows= loops=)

  • Output: (first_value(raw.file_id) OVER (?)), raw.series_id, (first_value(raw."row") OVER (?)), times."time", (first_value(raw.value) OVER (?)), cr1000tables.order2, files.size, raw.file_id, raw."row
  • Sort Key: raw.series_id, times."time", (first_value(raw.file_id) OVER (?)), (first_value(raw."row") OVER (?)), (first_value(raw.value) OVER (?))
  • Sort Method: external sort Disk: 118964800kB
2439314.020 27683616.798 ↓ 40.8 2253721890 1
→

WindowAgg (cost=280918844.53..282852354.29 rows=55243136 width=32) (actual time=21755061.936..27683616.798 rows=2253721890 loops=1)

10896251.733 25244302.778 ↓ 40.8 2253721890 1
→

Sort (cost=280918844.53..281056952.37 rows=55243136 width=32) (actual time=21755061.882..25244302.778 rows=2253721890 loops=1)

  • Sort Key: raw.series_id, times."time", cr1000tables.order2, files.size, raw.file_id, raw."row
  • Sort Method: external merge Disk: 101339168kB
1013238.874 14348051.045 ↓ 40.8 2253721890 1
→

Merge Join (cost=269886015.26..272493036.33 rows=55243136 width=32) (actual time=9743745.571..14348051.045 rows=2253721890 loops=1)

  • Merge Cond: ((raw."row" = times."row") AND (raw.file_id = times.file_id))
8445963.753 12831118.276 ↓ 17.0 2253721890 1
→

Sort (cost=252304433.49..252635868.38 rows=132573954 width=28) (actual time=9494631.550..12831118.276 rows=2253721890 loops=1)

  • Sort Key: raw."row", raw.file_id
  • Sort Method: external merge Disk: 83714784kB
680480.179 4385154.523 ↓ 17.0 2253721890 1
→

Hash Join (cost=1041.62..231246796.12 rows=132573954 width=28) (actual time=328.348..4385154.523 rows=2253721890 loops=1)

  • Hash Cond: (raw.file_id = files.file_id)
1805931.622 3704669.877 ↓ 7.1 2253721890 1
→

Hash Join (cost=445.50..226341223.34 rows=318148050 width=16) (actual time=323.846..3704669.877 rows=2253721890 loops=1)

  • Hash Cond: (raw.series_id = series.series_id)
1898700.476 1898700.476 ↓ 1.0 11650047471 1
→

Seq Scan on raw (cost=0.00..179473699.76 rows=11650046976 width=16) (actual time=103.892..1898700.476 rows=11650047471 loops=1)

0.282 37.779 ↓ 2.1 983 1
→

Hash (cost=439.52..439.52 rows=478 width=4) (actual time=37.779..37.779 rows=983 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
0.961 37.497 ↓ 2.1 983 1
→

Hash Join (cost=20.09..439.52 rows=478 width=4) (actual time=0.336..37.497 rows=983 loops=1)

  • Hash Cond: (series.alias_id = aliases.alias_id)
36.247 36.247 ↑ 1.0 7617 1
→

Seq Scan on series (cost=0.00..385.94 rows=7653 width=8) (actual time=0.025..36.247 rows=7617 loops=1)

  • Filter: (resolution <= '00:05:00'::interval)
0.016 0.289 ↓ 2.3 101 1
→

Hash (cost=19.55..19.55 rows=43 width=4) (actual time=0.289..0.289 rows=101 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
0.112 0.273 ↓ 2.3 101 1
→

Hash Join (cost=3.70..19.55 rows=43 width=4) (actual time=0.076..0.273 rows=101 loops=1)

  • Hash Cond: (aliases.variable_id = variables.variable_id)
0.121 0.121 ↑ 1.0 685 1
→

Seq Scan on aliases (cost=0.00..12.85 rows=685 width=8) (actual time=0.011..0.121 rows=685 loops=1)

0.005 0.040 ↓ 1.1 9 1
→

Hash (cost=3.60..3.60 rows=8 width=4) (actual time=0.040..0.040 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
0.035 0.035 ↓ 1.1 9 1
→

Seq Scan on variables (cost=0.00..3.60 rows=8 width=4) (actual time=0.015..0.035 rows=9 loops=1)

  • Filter: (priority = (-1))
0.702 4.467 ↑ 2.3 2996 1
→

Hash (cost=510.80..510.80 rows=6826 width=12) (actual time=4.467..4.467 rows=2996 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 129kB
1.801 3.765 ↑ 2.3 2996 1
→

Hash Join (cost=3.31..510.80 rows=6826 width=12) (actual time=0.049..3.765 rows=2996 loops=1)

  • Hash Cond: (files.cr1000table_id = cr1000tables.cr1000table_id)
1.934 1.934 ↑ 1.9 8595 1
→

Seq Scan on files (cost=0.00..377.80 rows=16380 width=12) (actual time=0.005..1.934 rows=8595 loops=1)

0.010 0.030 ↑ 1.0 36 1
→

Hash (cost=2.86..2.86 rows=36 width=8) (actual time=0.030..0.030 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
0.020 0.020 ↑ 1.0 36 1
→

Seq Scan on cr1000tables (cost=0.00..2.86 rows=36 width=8) (actual time=0.012..0.020 rows=36 loops=1)

  • Filter: (order2 IS NOT NULL)
159293.417 503693.895 ↓ 21.5 2276954179 1
→

Materialize (cost=17581508.13..18111724.29 rows=106043232 width=16) (actual time=249114.009..503693.895 rows=2276954179 loops=1)

319456.055 344400.478 ↑ 1.0 106043231 1
→

Sort (cost=17581508.13..17846616.21 rows=106043232 width=16) (actual time=249114.005..344400.478 rows=106043231 loops=1)

  • Sort Key: times."row", times.file_id
  • Sort Method: external merge Disk: 2695112kB
24944.423 24944.423 ↑ 1.0 106043231 1
→

Seq Scan on times (cost=0.00..1633639.32 rows=106043232 width=16) (actual time=40.813..24944.423 rows=106043231 loops=1)