explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hTWWp

Settings
# exclusive inclusive rows x rows loops node
1. 54.182 33,014.193 ↑ 3.3 95,040 1

Sort (cost=4,501,320.57..4,502,093.19 rows=309,046 width=345) (actual time=33,006.632..33,014.193 rows=95,040 loops=1)

  • Sort Key: signal."timestamp
  • Sort Method: quicksort Memory: 28318kB
  • Planning time: 15.094 ms
  • Execution time: 33030.132 ms
2. 40.198 32,960.011 ↑ 3.3 95,040 1

GroupAggregate (cost=4,461,550.27..4,473,139.50 rows=309,046 width=345) (actual time=32,906.680..32,960.011 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. 108.239 32,919.813 ↑ 3.3 95,040 1

Sort (cost=4,461,550.27..4,462,322.89 rows=309,046 width=245) (actual time=32,906.671..32,919.813 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. 20.492 32,811.574 ↑ 3.3 95,040 1

Hash Join (cost=1,257,876.23..4,433,369.20 rows=309,046 width=245) (actual time=1,020.712..32,811.574 rows=95,040 loops=1)

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

Hash Left Join (cost=1,257,874.10..4,429,117.69 rows=309,046 width=185) (actual time=1,020.151..32,790.536 rows=95,040 loops=1)

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

Hash Join (cost=1,257,870.98..4,424,865.18 rows=309,046 width=157) (actual time=1,019.570..32,769.162 rows=95,040 loops=1)

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

Hash Join (cost=1,257,869.73..4,418,772.64 rows=800,223 width=97) (actual time=1,019.166..32,740.507 rows=174,240 loops=1)

  • Hash Cond: (signal_to_quality.signal_id = signal.signal_id)
8. 13,641.487 13,641.487 ↑ 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.020..13,641.487 rows=173,476,658 loops=1)

9. 37.786 664.620 ↑ 5.3 126,720 1

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

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

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

  • Hash Cond: (signals_comments_association.signal_id = signal.signal_id)
11. 0.006 0.006 ↓ 0.0 0 1

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

12. 48.049 585.153 ↑ 5.3 126,720 1

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

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

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

14. 0.023 0.362 ↑ 1.0 1 1

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

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

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

16. 0.006 0.036 ↑ 1.0 1 1

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

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

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

  • Filter: ((name)::text = 'bf_f06_11e_russikerstr'::text)
  • Rows Removed by Filter: 96
18. 444.376 518.204 ↑ 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=74.883..518.204 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
19. 73.828 73.828 ↑ 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=73.828..73.828 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))
20. 0.009 0.375 ↑ 1.0 11 1

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

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

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

22. 0.012 0.554 ↑ 1.0 50 1

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

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

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

24. 0.017 0.546 ↑ 1.0 50 1

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

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

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