explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xecm

Settings
# exclusive inclusive rows x rows loops node
1. 5.336 72,248.617 ↑ 1,814.9 5,111 1

Sort (cost=3,978,274.89..4,001,464.53 rows=9,275,856 width=52) (actual time=72,247.901..72,248.617 rows=5,111 loops=1)

  • Sort Key: (min(signal."timestamp"))
  • Sort Method: quicksort Memory: 911kB
2. 8,329.465 72,243.281 ↑ 1,814.9 5,111 1

HashAggregate (cost=2,788,875.98..2,904,824.18 rows=9,275,856 width=52) (actual time=72,221.232..72,243.281 rows=5,111 loops=1)

  • Group Key: date_trunc('week'::text, signal."timestamp"), parameter.name, source.name
3. 7,581.255 63,913.816 ↓ 2.1 19,899,717 1

Hash Join (cost=486,022.31..2,649,738.14 rows=9,275,856 width=52) (actual time=26,410.158..63,913.816 rows=19,899,717 loops=1)

  • Hash Cond: (signal.source_id = source.source_id)
4. 4,954.452 56,332.502 ↓ 2.1 19,899,717 1

Hash Join (cost=486,017.12..2,499,000.30 rows=9,275,856 width=32) (actual time=26,408.437..56,332.502 rows=19,899,717 loops=1)

  • Hash Cond: (signal.parameter_id = parameter.parameter_id)
5. 25,030.598 51,378.029 ↓ 1.1 19,899,717 1

Bitmap Heap Scan on signal (cost=486,015.63..2,336,671.32 rows=18,551,713 width=24) (actual time=26,408.380..51,378.029 rows=19,899,717 loops=1)

  • Recheck Cond: (('2019-01-01 00:00:00'::timestamp without time zone <= "timestamp") AND ("timestamp" <= '2019-03-07 16:56:51'::timestamp without time zone))
  • Heap Blocks: exact=222848
6. 26,347.431 26,347.431 ↓ 1.1 21,001,060 1

Bitmap Index Scan on ix_signal_timestamp (cost=0.00..481,377.70 rows=18,551,713 width=0) (actual time=26,347.431..26,347.431 rows=21,001,060 loops=1)

  • Index Cond: (('2019-01-01 00:00:00'::timestamp without time zone <= "timestamp") AND ("timestamp" <= '2019-03-07 16:56:51'::timestamp without time zone))
7. 0.011 0.021 ↓ 2.3 50 1

Hash (cost=1.22..1.22 rows=22 width=16) (actual time=0.021..0.021 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
8. 0.010 0.010 ↓ 2.3 50 1

Seq Scan on parameter (cost=0.00..1.22 rows=22 width=16) (actual time=0.004..0.010 rows=50 loops=1)

9. 0.020 0.059 ↑ 1.0 97 1

Hash (cost=3.97..3.97 rows=97 width=28) (actual time=0.059..0.059 rows=97 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
10. 0.039 0.039 ↑ 1.0 97 1

Seq Scan on source (cost=0.00..3.97 rows=97 width=28) (actual time=0.015..0.039 rows=97 loops=1)