explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iIwj

Settings
# exclusive inclusive rows x rows loops node
1. 71.621 14,987.285 ↑ 5.3 126,720 1

Sort (cost=2,583,102.73..2,584,780.76 rows=671,215 width=185) (actual time=14,964.654..14,987.285 rows=126,720 loops=1)

  • Sort Key: signal."timestamp
  • Sort Method: quicksort Memory: 20893kB
2. 178.474 14,915.664 ↑ 5.3 126,720 1

HashAggregate (cost=2,511,428.99..2,518,141.14 rows=671,215 width=185) (actual time=14,860.596..14,915.664 rows=126,720 loops=1)

  • Group Key: signal.signal_id, parameter.name, parameter.unit, source_type.name, source.name, source.serial, site.name
3. 38.917 14,737.190 ↑ 3.9 174,240 1

Hash Join (cost=1,257,874.98..2,498,004.69 rows=671,215 width=181) (actual time=3,053.314..14,737.190 rows=174,240 loops=1)

  • Hash Cond: (signal.parameter_id = parameter.parameter_id)
4. 38.825 14,698.239 ↑ 3.9 174,240 1

Hash Left Join (cost=1,257,872.86..2,488,773.36 rows=671,215 width=121) (actual time=3,053.264..14,698.239 rows=174,240 loops=1)

  • Hash Cond: (signal.site_id = site.site_id)
5. 9,221.181 14,659.374 ↑ 3.9 174,240 1

Hash Right Join (cost=1,257,869.73..2,479,541.03 rows=671,215 width=93) (actual time=3,053.185..14,659.374 rows=174,240 loops=1)

  • Hash Cond: (signals_signal_quality_association.signal_id = signal.signal_id)
6. 5,276.603 5,276.603 ↑ 1.0 67,047,743 1

Seq Scan on signals_signal_quality_association (cost=0.00..967,149.44 rows=67,047,744 width=4) (actual time=0.030..5,276.603 rows=67,047,743 loops=1)

7. 38.956 161.590 ↑ 5.3 126,720 1

Hash (cost=1,249,479.54..1,249,479.54 rows=671,215 width=93) (actual time=161.590..161.590 rows=126,720 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 21186kB
8. 39.501 122.634 ↑ 5.3 126,720 1

Hash Right Join (cost=1,249,438.37..1,249,479.54 rows=671,215 width=93) (actual time=87.232..122.634 rows=126,720 loops=1)

  • Hash Cond: (signals_comments_association.signal_id = signal.signal_id)
9. 0.005 0.005 ↓ 0.0 0 1

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

10. 39.530 83.128 ↑ 5.3 126,720 1

Hash (cost=1,241,048.18..1,241,048.18 rows=671,215 width=89) (actual time=83.128..83.128 rows=126,720 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 20196kB
11. 17.180 43.598 ↑ 5.3 126,720 1

Nested Loop (cost=18,917.79..1,241,048.18 rows=671,215 width=89) (actual time=10.954..43.598 rows=126,720 loops=1)

12. 0.022 0.074 ↑ 1.0 1 1

Hash Join (cost=3.23..4.52 rows=1 width=65) (actual time=0.067..0.074 rows=1 loops=1)

  • Hash Cond: (source_type.source_type_id = source.source_type_id)
13. 0.021 0.021 ↑ 1.0 21 1

Seq Scan on source_type (cost=0.00..1.21 rows=21 width=36) (actual time=0.019..0.021 rows=21 loops=1)

14. 0.004 0.031 ↑ 1.0 1 1

Hash (cost=3.21..3.21 rows=1 width=37) (actual time=0.031..0.031 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.027 0.027 ↑ 1.0 1 1

Seq Scan on source (cost=0.00..3.21 rows=1 width=37) (actual time=0.019..0.027 rows=1 loops=1)

  • Filter: ((name)::text = 'bf_f06_11e_russikerstr'::text)
  • Rows Removed by Filter: 96
16. 15.681 26.344 ↑ 5.3 126,720 1

Bitmap Heap Scan on signal (cost=18,914.56..1,234,331.51 rows=671,215 width=32) (actual time=10.880..26.344 rows=126,720 loops=1)

  • Recheck Cond: ((source_id = source.source_id) AND ('2018-06-01 00:00:00'::timestamp without time zone <= "timestamp") AND ("timestamp" <= '2019-03-07 16:56:51'::timestamp without time zone))
  • Heap Blocks: exact=1374
17. 10.663 10.663 ↑ 5.3 126,720 1

Bitmap Index Scan on ix_timetamp_source_id (cost=0.00..18,746.76 rows=671,215 width=0) (actual time=10.663..10.663 rows=126,720 loops=1)

  • Index Cond: ((source_id = source.source_id) AND ('2018-06-01 00:00:00'::timestamp without time zone <= "timestamp") AND ("timestamp" <= '2019-03-07 16:56:51'::timestamp without time zone))
18. 0.016 0.040 ↑ 1.0 50 1

Hash (cost=2.50..2.50 rows=50 width=36) (actual time=0.040..0.040 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
19. 0.024 0.024 ↑ 1.0 50 1

Seq Scan on site (cost=0.00..2.50 rows=50 width=36) (actual time=0.014..0.024 rows=50 loops=1)

20. 0.014 0.034 ↑ 1.0 50 1

Hash (cost=1.50..1.50 rows=50 width=68) (actual time=0.034..0.034 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
21. 0.020 0.020 ↑ 1.0 50 1

Seq Scan on parameter (cost=0.00..1.50 rows=50 width=68) (actual time=0.013..0.020 rows=50 loops=1)