explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ECo

Settings
# exclusive inclusive rows x rows loops node
1. 123.287 14,591.156 ↑ 3.9 174,240 1

Sort (cost=2,604,929.45..2,606,607.49 rows=671,215 width=345) (actual time=14,553.454..14,591.156 rows=174,240 loops=1)

  • Sort Key: signal."timestamp
  • Sort Method: quicksort Memory: 50447kB
  • Planning time: 3.292 ms
  • Execution time: 14623.545 ms
2. 226.321 14,467.869 ↑ 3.9 174,240 1

HashAggregate (cost=2,533,255.72..2,539,967.87 rows=671,215 width=345) (actual time=14,374.275..14,467.869 rows=174,240 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. 31.743 14,241.548 ↑ 3.9 174,240 1

Hash Left Join (cost=1,257,887.22..2,516,475.34 rows=671,215 width=245) (actual time=2,828.345..14,241.548 rows=174,240 loops=1)

  • Hash Cond: (signal_quality.quality_id = quality.quality_id)
4. 32.789 14,209.795 ↑ 3.9 174,240 1

Hash Left Join (cost=1,257,885.97..2,507,244.89 rows=671,215 width=185) (actual time=2,828.318..14,209.795 rows=174,240 loops=1)

  • Hash Cond: (signals_signal_quality_association.signal_quality_id = signal_quality.signal_quality_id)
5. 40.507 14,176.891 ↑ 3.9 174,240 1

Hash Join (cost=1,257,874.98..2,498,004.69 rows=671,215 width=185) (actual time=2,828.177..14,176.891 rows=174,240 loops=1)

  • Hash Cond: (signal.parameter_id = parameter.parameter_id)
6. 42.187 14,136.359 ↑ 3.9 174,240 1

Hash Left Join (cost=1,257,872.86..2,488,773.36 rows=671,215 width=125) (actual time=2,828.134..14,136.359 rows=174,240 loops=1)

  • Hash Cond: (signal.site_id = site.site_id)
7. 8,698.139 14,094.129 ↑ 3.9 174,240 1

Hash Right Join (cost=1,257,869.73..2,479,541.03 rows=671,215 width=97) (actual time=2,828.050..14,094.129 rows=174,240 loops=1)

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

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

9. 38.920 154.832 ↑ 5.3 126,720 1

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

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

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

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

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

12. 34.625 75.908 ↑ 5.3 126,720 1

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

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

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

14. 0.025 0.079 ↑ 1.0 1 1

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

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

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

16. 0.005 0.032 ↑ 1.0 1 1

Hash (cost=3.21..3.21 rows=1 width=37) (actual time=0.032..0.032 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. 14.674 24.827 ↑ 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.334..24.827 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. 10.153 10.153 ↑ 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.152..10.153 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.019 0.043 ↑ 1.0 50 1

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

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

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

22. 0.015 0.025 ↑ 1.0 50 1

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

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

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

24. 0.066 0.115 ↑ 1.0 355 1

Hash (cost=6.55..6.55 rows=355 width=8) (actual time=0.115..0.115 rows=355 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
25. 0.049 0.049 ↑ 1.0 355 1

Seq Scan on signal_quality (cost=0.00..6.55 rows=355 width=8) (actual time=0.007..0.049 rows=355 loops=1)

26. 0.006 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
27. 0.004 0.004 ↑ 1.0 11 1

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