explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 32TU

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 2,340.359 ↑ 1.0 20 1

Limit (cost=809,591.53..809,591.58 rows=20 width=86) (actual time=2,340.355..2,340.359 rows=20 loops=1)

2. 144.788 2,340.355 ↑ 6,826.2 20 1

Sort (cost=809,591.53..809,932.84 rows=136,525 width=86) (actual time=2,340.354..2,340.355 rows=20 loops=1)

  • Sort Key: signal."timestamp" DESC
  • Sort Method: top-N heapsort Memory: 27kB
3. 984.878 2,195.567 ↓ 5.4 735,598 1

HashAggregate (cost=804,593.40..805,958.65 rows=136,525 width=86) (actual time=1,840.675..2,195.567 rows=735,598 loops=1)

  • Group Key: signal.signal_id, parameter.name, parameter.unit, source_type.name, source.name, source.serial
4. 166.774 1,210.689 ↓ 5.4 735,598 1

Hash Join (cost=798,254.79..802,204.21 rows=136,525 width=86) (actual time=613.547..1,210.689 rows=735,598 loops=1)

  • Hash Cond: (signal.site_id = site.site_id)
5. 193.447 1,043.894 ↓ 5.2 735,598 1

Hash Join (cost=798,251.71..800,302.59 rows=142,213 width=90) (actual time=613.519..1,043.894 rows=735,598 loops=1)

  • Hash Cond: (signal.source_id = source.source_id)
6. 237.290 850.360 ↓ 4.7 735,598 1

Hash Right Join (cost=798,244.01..798,285.11 rows=158,014 width=47) (actual time=613.416..850.360 rows=735,598 loops=1)

  • Hash Cond: (signals_comments_association.signal_id = signal.signal_id)
7. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on signals_comments_association (cost=0.00..32.60 rows=2,260 width=8) (actual time=0.002..0.002 rows=0 loops=1)

8. 214.014 613.068 ↓ 4.7 735,598 1

Hash (cost=796,268.84..796,268.84 rows=158,014 width=43) (actual time=613.068..613.068 rows=735,598 loops=1)

  • Buckets: 1048576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 65307kB
9. 202.517 399.054 ↓ 4.7 735,598 1

Hash Join (cost=7,463.29..796,268.84 rows=158,014 width=43) (actual time=111.275..399.054 rows=735,598 loops=1)

  • Hash Cond: (signal.parameter_id = parameter.parameter_id)
10. 86.551 196.519 ↓ 2.3 735,598 1

Bitmap Heap Scan on signal (cost=7,461.80..793,502.09 rows=316,029 width=32) (actual time=111.241..196.519 rows=735,598 loops=1)

  • Recheck Cond: ("timestamp" >= (now() - '3 days'::interval))
  • Heap Blocks: exact=8181
11. 109.968 109.968 ↓ 2.3 735,598 1

Bitmap Index Scan on ix_signal_timestamp (cost=0.00..7,382.79 rows=316,029 width=0) (actual time=109.968..109.968 rows=735,598 loops=1)

  • Index Cond: ("timestamp" >= (now() - '3 days'::interval))
12. 0.007 0.018 ↓ 2.3 50 1

Hash (cost=1.22..1.22 rows=22 width=19) (actual time=0.018..0.018 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
13. 0.011 0.011 ↓ 2.3 50 1

Seq Scan on parameter (cost=0.00..1.22 rows=22 width=19) (actual time=0.003..0.011 rows=50 loops=1)

14. 0.032 0.087 ↓ 1.1 97 1

Hash (cost=6.61..6.61 rows=87 width=51) (actual time=0.087..0.087 rows=97 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
15. 0.037 0.055 ↓ 1.1 97 1

Hash Join (cost=1.41..6.61 rows=87 width=51) (actual time=0.017..0.055 rows=97 loops=1)

  • Hash Cond: (source.source_type_id = source_type.source_type_id)
16. 0.009 0.009 ↑ 1.0 97 1

Seq Scan on source (cost=0.00..3.97 rows=97 width=37) (actual time=0.003..0.009 rows=97 loops=1)

17. 0.004 0.009 ↓ 1.2 21 1

Hash (cost=1.18..1.18 rows=18 width=22) (actual time=0.009..0.009 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
18. 0.005 0.005 ↓ 1.2 21 1

Seq Scan on source_type (cost=0.00..1.18 rows=18 width=22) (actual time=0.002..0.005 rows=21 loops=1)

19. 0.011 0.021 ↓ 1.0 50 1

Hash (cost=2.48..2.48 rows=48 width=4) (actual time=0.021..0.021 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
20. 0.010 0.010 ↓ 1.0 50 1

Seq Scan on site (cost=0.00..2.48 rows=48 width=4) (actual time=0.005..0.010 rows=50 loops=1)