explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KMfe

Settings
# exclusive inclusive rows x rows loops node
1. 247.246 4,006.075 ↑ 3.4 390,912 1

Sort (cost=2,905,139.20..2,908,486.14 rows=1,338,777 width=167) (actual time=3,928.107..4,006.075 rows=390,912 loops=1)

  • Sort Key: signal."timestamp
  • Sort Method: quicksort Memory: 91797kB
2.          

CTE source_ids

3. 0.010 0.026 ↑ 2.5 2 1

Nested Loop (cost=0.00..5.09 rows=5 width=4) (actual time=0.017..0.026 rows=2 loops=1)

  • Join Filter: (source_1.source_type_id = source_type_1.source_type_id)
  • Rows Removed by Join Filter: 81
4. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on source_type source_type_1 (cost=0.00..1.23 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=1)

  • Filter: ((name)::text = 'Hach_Flo-Dar'::text)
  • Rows Removed by Filter: 18
5. 0.007 0.007 ↑ 1.0 83 1

Seq Scan on source source_1 (cost=0.00..2.83 rows=83 width=8) (actual time=0.001..0.007 rows=83 loops=1)

6.          

Initplan (forSort)

7. 0.029 0.029 ↑ 2.5 2 1

CTE Scan on source_ids (cost=0.00..0.10 rows=5 width=4) (actual time=0.019..0.029 rows=2 loops=1)

8. 437.277 3,758.800 ↑ 3.4 390,912 1

HashAggregate (cost=2,755,509.05..2,768,896.82 rows=1,338,777 width=167) (actual time=3,577.361..3,758.800 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
9. 236.883 3,321.523 ↑ 3.4 390,912 1

Nested Loop Left Join (cost=2,641,176.67..2,722,039.62 rows=1,338,777 width=167) (actual time=2,964.983..3,321.523 rows=390,912 loops=1)

  • Join Filter: (quality.quality_id = signal_quality.quality_id)
  • Rows Removed by Join Filter: 1077609
10. 75.392 3,084.640 ↑ 3.4 390,912 1

Merge Right Join (cost=2,641,176.67..2,661,793.62 rows=1,338,777 width=107) (actual time=2,964.958..3,084.640 rows=390,912 loops=1)

  • Merge Cond: (signal_quality.signal_quality_id = signals_signal_quality_association.signal_quality_id)
11. 0.020 0.020 ↑ 5,036.2 4 1

Index Scan using signal_quality_pkey on signal_quality (cost=0.29..662.46 rows=20,145 width=8) (actual time=0.012..0.020 rows=4 loops=1)

12. 150.547 3,009.228 ↑ 3.4 390,912 1

Sort (cost=2,641,176.38..2,644,523.33 rows=1,338,777 width=107) (actual time=2,964.941..3,009.228 rows=390,912 loops=1)

  • Sort Key: signals_signal_quality_association.signal_quality_id
  • Sort Method: quicksort Memory: 71265kB
13. 375.597 2,858.681 ↑ 3.4 390,912 1

Merge Left Join (cost=2,470,424.05..2,504,939.19 rows=1,338,777 width=107) (actual time=2,366.085..2,858.681 rows=390,912 loops=1)

  • Merge Cond: (signal.signal_id = signals_signal_quality_association.signal_id)
14. 59.633 911.076 ↑ 3.4 390,912 1

Merge Left Join (cost=1,867,362.93..1,874,068.54 rows=1,338,777 width=103) (actual time=822.208..911.076 rows=390,912 loops=1)

  • Merge Cond: (signal.signal_id = signals_comments_association.signal_id)
15. 161.527 851.426 ↑ 3.4 390,912 1

Sort (cost=1,867,204.43..1,870,551.37 rows=1,338,777 width=99) (actual time=822.188..851.426 rows=390,912 loops=1)

  • Sort Key: signal.signal_id
  • Sort Method: quicksort Memory: 67261kB
16. 65.373 689.899 ↑ 3.4 390,912 1

Merge Join (cost=1,711,365.79..1,730,967.24 rows=1,338,777 width=99) (actual time=597.953..689.899 rows=390,912 loops=1)

  • Merge Cond: (source.source_id = signal.source_id)
17. 0.034 0.110 ↑ 1.4 61 1

Sort (cost=10.86..11.07 rows=83 width=52) (actual time=0.100..0.110 rows=61 loops=1)

  • Sort Key: source.source_id
  • Sort Method: quicksort Memory: 34kB
18. 0.018 0.076 ↑ 1.0 83 1

Merge Join (cost=7.03..8.22 rows=83 width=52) (actual time=0.053..0.076 rows=83 loops=1)

  • Merge Cond: (source_type.source_type_id = source.source_type_id)
19. 0.011 0.017 ↓ 1.1 19 1

Sort (cost=1.56..1.60 rows=18 width=22) (actual time=0.016..0.017 rows=19 loops=1)

  • Sort Key: source_type.source_type_id
  • Sort Method: quicksort Memory: 26kB
20. 0.006 0.006 ↓ 1.1 19 1

Seq Scan on source_type (cost=0.00..1.18 rows=18 width=22) (actual time=0.003..0.006 rows=19 loops=1)

21. 0.026 0.041 ↑ 1.0 83 1

Sort (cost=5.48..5.68 rows=83 width=38) (actual time=0.035..0.041 rows=83 loops=1)

  • Sort Key: source.source_type_id
  • Sort Method: quicksort Memory: 31kB
22. 0.015 0.015 ↑ 1.0 83 1

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

23. 103.246 624.416 ↑ 3.4 390,912 1

Sort (cost=1,711,354.93..1,714,701.87 rows=1,338,777 width=55) (actual time=597.842..624.416 rows=390,912 loops=1)

  • Sort Key: signal.source_id
  • Sort Method: quicksort Memory: 67261kB
24. 63.669 521.170 ↑ 3.4 390,912 1

Merge Join (cost=1,556,539.51..1,575,117.74 rows=1,338,777 width=55) (actual time=431.342..521.170 rows=390,912 loops=1)

  • Merge Cond: (site.site_id = signal.site_id)
25. 0.012 0.023 ↑ 1.2 40 1

Sort (cost=3.82..3.94 rows=48 width=20) (actual time=0.020..0.023 rows=40 loops=1)

  • Sort Key: site.site_id
  • Sort Method: quicksort Memory: 28kB
26. 0.011 0.011 ↑ 1.0 48 1

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

27. 96.663 457.478 ↑ 3.4 390,912 1

Sort (cost=1,556,535.69..1,559,882.63 rows=1,338,777 width=43) (actual time=431.318..457.478 rows=390,912 loops=1)

  • Sort Key: signal.site_id
  • Sort Method: quicksort Memory: 53009kB
28. 60.040 360.815 ↑ 3.4 390,912 1

Merge Join (cost=1,397,074.54..1,420,298.50 rows=1,338,777 width=43) (actual time=275.161..360.815 rows=390,912 loops=1)

  • Merge Cond: (parameter.parameter_id = signal.parameter_id)
29. 0.014 0.024 ↓ 1.5 33 1

Sort (cost=1.71..1.77 rows=22 width=19) (actual time=0.018..0.024 rows=33 loops=1)

  • Sort Key: parameter.parameter_id
  • Sort Method: quicksort Memory: 28kB
30. 0.010 0.010 ↓ 2.1 46 1

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

31. 89.056 300.751 ↑ 6.1 390,912 1

Sort (cost=1,397,072.83..1,403,006.04 rows=2,373,286 width=32) (actual time=275.139..300.751 rows=390,912 loops=1)

  • Sort Key: signal.parameter_id
  • Sort Method: quicksort Memory: 42829kB
32. 158.543 211.695 ↑ 6.1 390,912 1

Bitmap Heap Scan on signal (cost=147,683.95..1,145,760.18 rows=2,373,286 width=32) (actual time=113.542..211.695 rows=390,912 loops=1)

  • Recheck Cond: (source_id = ANY ($1))
  • Filter: (('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 Filter: 547834
  • Heap Blocks: exact=9997
33. 53.152 53.152 ↑ 8.8 938,746 1

Bitmap Index Scan on signal_source_id_index (cost=0.00..147,090.62 rows=8,231,063 width=0) (actual time=53.152..53.152 rows=938,746 loops=1)

  • Index Cond: (source_id = ANY ($1))
34. 0.015 0.017 ↓ 0.0 0 1

Sort (cost=158.51..164.16 rows=2,260 width=8) (actual time=0.017..0.017 rows=0 loops=1)

  • Sort Key: signals_comments_association.signal_id
  • Sort Method: quicksort Memory: 25kB
35. 0.002 0.002 ↓ 0.0 0 1

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

36. 1,220.446 1,572.008 ↑ 1.1 4,564,768 1

Sort (cost=603,061.12..615,079.15 rows=4,807,213 width=8) (actual time=1,282.336..1,572.008 rows=4,564,768 loops=1)

  • Sort Key: signals_signal_quality_association.signal_id
  • Sort Method: quicksort Memory: 421947kB
37. 351.562 351.562 ↑ 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.048..351.562 rows=4,807,213 loops=1)

38. 0.000 0.000 ↑ 1.0 3 390,912

Materialize (cost=0.00..1.04 rows=3 width=68) (actual time=0.000..0.000 rows=3 loops=390,912)

39. 0.011 0.011 ↑ 1.0 3 1

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