explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M5EB

Settings
# exclusive inclusive rows x rows loops node
1. 388.593 10,948.900 ↑ 1.6 390,912 1

Sort (cost=2,188,371.06..2,189,981.33 rows=644,109 width=167) (actual time=10,892.884..10,948.900 rows=390,912 loops=1)

  • Sort Key: signal."timestamp
  • Sort Method: external sort Disk: 47040kB
2. 248.743 10,560.307 ↑ 1.6 390,912 1

GroupAggregate (cost=1,996,391.29..2,020,545.38 rows=644,109 width=167) (actual time=10,137.164..10,560.307 rows=390,912 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. 514.115 10,311.564 ↑ 1.6 390,912 1

Sort (cost=1,996,391.29..1,998,001.56 rows=644,109 width=167) (actual time=10,137.153..10,311.564 rows=390,912 loops=1)

  • Sort Key: signal.signal_id, parameter.name, parameter.unit, source_type.name, source.name, source.serial, site.name, quality.method, quality.flag
  • Sort Method: external merge Disk: 44592kB
4. 59.893 9,797.449 ↑ 1.6 390,912 1

Hash Left Join (cost=1,667,002.55..1,828,565.61 rows=644,109 width=167) (actual time=8,187.400..9,797.449 rows=390,912 loops=1)

  • Hash Cond: (signal_quality.quality_id = quality.quality_id)
5. 64.398 9,737.547 ↑ 1.6 390,912 1

Hash Left Join (cost=1,667,001.49..1,819,708.04 rows=644,109 width=107) (actual time=8,187.382..9,737.547 rows=390,912 loops=1)

  • Hash Cond: (signals_signal_quality_association.signal_quality_id = signal_quality.signal_quality_id)
6. 87.067 9,667.643 ↑ 1.6 390,912 1

Hash Left Join (cost=1,666,419.22..1,810,269.28 rows=644,109 width=107) (actual time=8,181.826..9,667.643 rows=390,912 loops=1)

  • Hash Cond: (signal.site_id = site.site_id)
7. 1,080.892 9,580.546 ↑ 1.6 390,912 1

Hash Right Join (cost=1,666,416.14..1,801,409.70 rows=644,109 width=95) (actual time=8,181.782..9,580.546 rows=390,912 loops=1)

  • Hash Cond: (signals_signal_quality_association.signal_id = signal.signal_id)
8. 420.216 420.216 ↑ 1.0 4,807,213 1

Seq Scan on signals_signal_quality_association (cost=0.00..69,538.13 rows=4,807,213 width=8) (actual time=0.022..420.216 rows=4,807,213 loops=1)

9. 112.940 8,079.438 ↑ 1.6 390,912 1

Hash (cost=1,648,928.78..1,648,928.78 rows=644,109 width=91) (actual time=8,079.438..8,079.438 rows=390,912 loops=1)

  • Buckets: 32768 Batches: 32 Memory Usage: 1713kB
10. 106.995 7,966.498 ↑ 1.6 390,912 1

Hash Right Join (cost=1,640,062.51..1,648,928.78 rows=644,109 width=91) (actual time=7,859.551..7,966.498 rows=390,912 loops=1)

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

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

12. 128.550 7,859.502 ↑ 1.6 390,912 1

Hash (cost=1,623,204.14..1,623,204.14 rows=644,109 width=87) (actual time=7,859.502..7,859.502 rows=390,912 loops=1)

  • Buckets: 32768 Batches: 32 Memory Usage: 1619kB
13. 92.848 7,730.952 ↑ 1.6 390,912 1

Hash Join (cost=443,010.22..1,623,204.14 rows=644,109 width=87) (actual time=2,308.073..7,730.952 rows=390,912 loops=1)

  • Hash Cond: (signal.parameter_id = parameter.parameter_id)
14. 1,738.832 7,638.081 ↑ 2.9 390,912 1

Hash Join (cost=443,008.72..1,612,479.70 rows=1,141,830 width=76) (actual time=2,308.043..7,638.081 rows=390,912 loops=1)

  • Hash Cond: (signal.source_id = source.source_id)
15. 3,607.273 5,899.218 ↑ 1.0 20,459,097 1

Bitmap Heap Scan on signal (cost=443,004.23..1,523,020.38 rows=20,552,943 width=32) (actual time=2,298.525..5,899.218 rows=20,459,097 loops=1)

  • Recheck Cond: (('2018-10-01 00:00:00'::timestamp without time zone <= "timestamp") AND ("timestamp" <= '2019-01-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Index Recheck: 29277
  • Heap Blocks: exact=31134 lossy=184383
16. 2,291.945 2,291.945 ↑ 1.0 20,459,097 1

Bitmap Index Scan on ix_signal_timestamp (cost=0.00..437,866.00 rows=20,552,943 width=0) (actual time=2,291.945..2,291.945 rows=20,459,097 loops=1)

  • Index Cond: (('2018-10-01 00:00:00'::timestamp without time zone <= "timestamp") AND ("timestamp" <= '2019-01-01 00:00:00'::timestamp without time zone))
17. 0.003 0.031 ↑ 2.5 2 1

Hash (cost=4.43..4.43 rows=5 width=52) (actual time=0.031..0.031 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.011 0.028 ↑ 2.5 2 1

Hash Join (cost=1.24..4.43 rows=5 width=52) (actual time=0.020..0.028 rows=2 loops=1)

  • Hash Cond: (source.source_type_id = source_type.source_type_id)
19. 0.009 0.009 ↑ 1.0 83 1

Seq Scan on source (cost=0.00..2.83 rows=83 width=38) (actual time=0.001..0.009 rows=83 loops=1)

20. 0.002 0.008 ↑ 1.0 1 1

Hash (cost=1.23..1.23 rows=1 width=22) (actual time=0.008..0.008 rows=1 loops=1)

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

Seq Scan on source_type (cost=0.00..1.23 rows=1 width=22) (actual time=0.005..0.006 rows=1 loops=1)

  • Filter: ((name)::text = 'Hach_Flo-Dar'::text)
  • Rows Removed by Filter: 18
22. 0.014 0.023 ↓ 2.1 46 1

Hash (cost=1.22..1.22 rows=22 width=19) (actual time=0.023..0.023 rows=46 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
23. 0.009 0.009 ↓ 2.1 46 1

Seq Scan on parameter (cost=0.00..1.22 rows=22 width=19) (actual time=0.005..0.009 rows=46 loops=1)

24. 0.007 0.030 ↑ 1.0 48 1

Hash (cost=2.48..2.48 rows=48 width=20) (actual time=0.030..0.030 rows=48 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
25. 0.023 0.023 ↑ 1.0 48 1

Seq Scan on site (cost=0.00..2.48 rows=48 width=20) (actual time=0.008..0.023 rows=48 loops=1)

26. 2.577 5.506 ↑ 1.0 20,145 1

Hash (cost=330.45..330.45 rows=20,145 width=8) (actual time=5.506..5.506 rows=20,145 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1043kB
27. 2.929 2.929 ↑ 1.0 20,145 1

Seq Scan on signal_quality (cost=0.00..330.45 rows=20,145 width=8) (actual time=0.009..2.929 rows=20,145 loops=1)

28. 0.001 0.009 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=68) (actual time=0.009..0.009 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.008 0.008 ↑ 1.0 3 1

Seq Scan on quality (cost=0.00..1.03 rows=3 width=68) (actual time=0.007..0.008 rows=3 loops=1)