explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u7Pf

Settings
# exclusive inclusive rows x rows loops node
1. 53.522 32,353.436 ↑ 3.3 95,040 1

Sort (cost=4,508,032.72..4,508,805.34 rows=309,046 width=345) (actual time=32,346.095..32,353.436 rows=95,040 loops=1)

  • Sort Key: signal."timestamp
  • Sort Method: quicksort Memory: 28318kB
  • Planning time: 3.556 ms
2. 39.812 32,299.914 ↑ 3.3 95,040 1

GroupAggregate (cost=4,468,262.42..4,479,851.65 rows=309,046 width=345) (actual time=32,247.482..32,299.914 rows=95,040 loops=1)

  • Group Key: signal.signal_id, parameter.unit, parameter.name, source.name, source.serial, source_type.name, site.name, quality.method, quality.flag
3. 107.319 32,260.102 ↑ 3.3 95,040 1

Sort (cost=4,468,262.42..4,469,035.04 rows=309,046 width=245) (actual time=32,247.474..32,260.102 rows=95,040 loops=1)

  • Sort Key: signal.signal_id, parameter.unit, parameter.name, source.serial, source_type.name, site.name, quality.method, quality.flag
  • Sort Method: quicksort Memory: 28318kB
4. 21.162 32,152.783 ↑ 3.3 95,040 1

Hash Join (cost=1,264,588.38..4,440,081.35 rows=309,046 width=245) (actual time=497.233..32,152.783 rows=95,040 loops=1)

  • Hash Cond: (signal.parameter_id = parameter.parameter_id)
5. 20.841 32,131.595 ↑ 3.3 95,040 1

Hash Left Join (cost=1,264,586.25..4,435,829.84 rows=309,046 width=185) (actual time=497.191..32,131.595 rows=95,040 loops=1)

  • Hash Cond: (signal.site_id = site.site_id)
6. 28.515 32,110.721 ↑ 3.3 95,040 1

Hash Join (cost=1,264,583.13..4,431,577.33 rows=309,046 width=157) (actual time=497.135..32,110.721 rows=95,040 loops=1)

  • Hash Cond: (signal_to_quality.quality_id = quality.quality_id)
7. 18,460.732 32,082.196 ↑ 4.6 174,240 1

Hash Join (cost=1,264,581.88..4,425,484.79 rows=800,223 width=97) (actual time=497.096..32,082.196 rows=174,240 loops=1)

  • Hash Cond: (signal_to_quality.signal_id = signal.signal_id)
8. 13,459.494 13,459.494 ↑ 1.0 173,476,658 1

Seq Scan on signal_to_quality (cost=0.00..2,502,363.04 rows=173,476,704 width=8) (actual time=0.011..13,459.494 rows=173,476,658 loops=1)

9. 39.434 161.970 ↑ 5.3 126,720 1

Hash (cost=1,256,191.69..1,256,191.69 rows=671,215 width=93) (actual time=161.970..161.970 rows=126,720 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 21186kB
10. 41.245 122.536 ↑ 5.3 126,720 1

Hash Right Join (cost=1,256,150.52..1,256,191.69 rows=671,215 width=93) (actual time=85.445..122.536 rows=126,720 loops=1)

  • Hash Cond: (signals_comments_association.signal_id = signal.signal_id)
11. 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)

12. 37.483 81.286 ↑ 5.3 126,720 1

Hash (cost=1,247,760.33..1,247,760.33 rows=671,215 width=89) (actual time=81.286..81.286 rows=126,720 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 20196kB
13. 16.647 43.803 ↑ 5.3 126,720 1

Nested Loop (cost=22,273.86..1,247,760.33 rows=671,215 width=89) (actual time=11.128..43.803 rows=126,720 loops=1)

14. 0.025 0.066 ↑ 1.0 1 1

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

  • Hash Cond: (source_type.source_type_id = source.source_type_id)
15. 0.008 0.008 ↑ 1.0 21 1

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

16. 0.006 0.033 ↑ 1.0 1 1

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

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

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

  • Filter: ((name)::text = 'bf_f06_11e_russikerstr'::text)
  • Rows Removed by Filter: 96
18. 16.206 27.090 ↑ 5.3 126,720 1

Bitmap Heap Scan on signal (cost=22,270.64..1,241,043.66 rows=671,215 width=32) (actual time=11.063..27.090 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) 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
19. 10.884 10.884 ↑ 5.3 126,720 1

Bitmap Index Scan on ix_timetamp_source_id (cost=0.00..22,102.83 rows=671,215 width=0) (actual time=10.883..10.884 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) AND ('2018-06-01 00:00:00'::timestamp without time zone <= "timestamp") AND ("timestamp" <= '2019-03-07 16:56:51'::timestamp without time zone))
20. 0.004 0.010 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=68) (actual time=0.010..0.010 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.006 0.006 ↑ 1.0 11 1

Seq Scan on quality (cost=0.00..1.11 rows=11 width=68) (actual time=0.006..0.006 rows=11 loops=1)

22. 0.014 0.033 ↑ 1.0 50 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
23. 0.019 0.019 ↑ 1.0 50 1

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

24. 0.011 0.026 ↑ 1.0 50 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
25. 0.015 0.015 ↑ 1.0 50 1

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