explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g34y

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 12,293.791 ↑ 1.0 20 1

Limit (cost=1,419,319.19..1,419,319.24 rows=20 width=86) (actual time=12,293.786..12,293.791 rows=20 loops=1)

2. 385.383 12,293.788 ↑ 17,636.8 20 1

Sort (cost=1,419,319.19..1,420,201.03 rows=352,735 width=86) (actual time=12,293.785..12,293.788 rows=20 loops=1)

  • Sort Key: signal."timestamp" DESC
  • Sort Method: top-N heapsort Memory: 27kB
3. 2,792.417 11,908.405 ↓ 5.6 1,960,150 1

HashAggregate (cost=1,406,405.69..1,409,933.04 rows=352,735 width=86) (actual time=10,888.214..11,908.405 rows=1,960,150 loops=1)

  • Group Key: signal.signal_id, parameter.name, parameter.unit, source_type.name, source.name, source.serial
4. 417.010 9,115.988 ↓ 5.6 1,960,150 1

Hash Join (cost=1,390,093.81..1,400,232.83 rows=352,735 width=86) (actual time=7,577.398..9,115.988 rows=1,960,150 loops=1)

  • Hash Cond: (signal.site_id = site.site_id)
5. 484.760 8,698.955 ↓ 5.3 1,960,150 1

Hash Join (cost=1,390,090.73..1,395,324.53 rows=367,432 width=90) (actual time=7,577.362..8,698.955 rows=1,960,150 loops=1)

  • Hash Cond: (signal.source_id = source.source_id)
6. 638.809 8,214.109 ↓ 4.8 1,960,150 1

Hash Right Join (cost=1,390,083.03..1,390,124.16 rows=408,258 width=47) (actual time=7,577.257..8,214.109 rows=1,960,150 loops=1)

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

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

8. 708.383 7,575.299 ↓ 4.8 1,960,150 1

Hash (cost=1,384,979.80..1,384,979.80 rows=408,258 width=43) (actual time=7,575.299..7,575.299 rows=1,960,150 loops=1)

  • Buckets: 2097152 (originally 524288) Batches: 1 (originally 1) Memory Usage: 168589kB
9. 536.971 6,866.916 ↓ 4.8 1,960,150 1

Hash Join (cost=19,282.08..1,384,979.80 rows=408,258 width=43) (actual time=2,524.107..6,866.916 rows=1,960,150 loops=1)

  • Hash Cond: (signal.parameter_id = parameter.parameter_id)
10. 3,810.327 6,329.920 ↓ 2.4 1,960,150 1

Bitmap Heap Scan on signal (cost=19,280.58..1,377,833.78 rows=816,517 width=32) (actual time=2,524.042..6,329.920 rows=1,960,150 loops=1)

  • Recheck Cond: ("timestamp" >= (now() - '7 days'::interval))
  • Heap Blocks: exact=21110
11. 2,519.593 2,519.593 ↓ 2.4 1,960,150 1

Bitmap Index Scan on ix_signal_timestamp (cost=0.00..19,076.45 rows=816,517 width=0) (actual time=2,519.593..2,519.593 rows=1,960,150 loops=1)

  • Index Cond: ("timestamp" >= (now() - '7 days'::interval))
12. 0.016 0.025 ↓ 2.3 50 1

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

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

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

14. 0.025 0.086 ↓ 1.1 97 1

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

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

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

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

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

17. 0.004 0.010 ↓ 1.2 21 1

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

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

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

19. 0.013 0.023 ↓ 1.0 50 1

Hash (cost=2.48..2.48 rows=48 width=4) (actual time=0.023..0.023 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.006..0.010 rows=50 loops=1)

Planning time : 1.597 ms
Execution time : 12,326.654 ms