explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cqzB

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

Sort (cost=2,604,929.45..2,606,607.49 rows=671,215 width=249) (actual time=14,846.610..14,881.758 rows=174,240 loops=1)

  • Sort Key: signal."timestamp
  • Sort Method: quicksort Memory: 46487kB
2. 225.859 14,766.439 ↑ 3.9 174,240 1

HashAggregate (cost=2,533,255.72..2,539,967.87 rows=671,215 width=249) (actual time=14,674.839..14,766.439 rows=174,240 loops=1)

  • Group Key: signal.signal_id, parameter.name, parameter.unit, source_type.name, source.name, source.serial, site.name, quality.method, quality.flag
3. 31.884 14,540.580 ↑ 3.9 174,240 1

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

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

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

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

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

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

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

  • Hash Cond: (signal.site_id = site.site_id)
7. 9,037.828 14,393.725 ↑ 3.9 174,240 1

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

  • Hash Cond: (signals_signal_quality_association.signal_id = signal.signal_id)
8. 5,205.020 5,205.020 ↑ 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.048..5,205.020 rows=67,047,743 loops=1)

9. 36.723 150.877 ↑ 5.3 126,720 1

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

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

Hash Right Join (cost=1,249,438.37..1,249,479.54 rows=671,215 width=93) (actual time=80.139..114.154 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. 34.595 76.302 ↑ 5.3 126,720 1

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

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

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

14. 0.020 0.069 ↑ 1.0 1 1

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

  • Hash Cond: (source_type.source_type_id = source.source_type_id)
15. 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.015..0.021 rows=21 loops=1)

16. 0.005 0.028 ↑ 1.0 1 1

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

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

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

  • Filter: ((name)::text = 'bf_f06_11e_russikerstr'::text)
  • Rows Removed by Filter: 96
18. 14.684 24.805 ↑ 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.300..24.805 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.121 10.121 ↑ 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.121..10.121 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.012 0.038 ↑ 1.0 50 1

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

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

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

22. 0.014 0.024 ↑ 1.0 50 1

Hash (cost=1.50..1.50 rows=50 width=68) (actual time=0.024..0.024 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.052 0.102 ↑ 1.0 355 1

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

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

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

26. 0.002 0.012 ↑ 1.0 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.010 0.010 ↑ 1.0 11 1

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