explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rcp3

Settings
# exclusive inclusive rows x rows loops node
1. 175.033 6,096.079 ↑ 3.4 390,912 1

Sort (cost=3,044,723.33..3,048,070.28 rows=1,338,777 width=167) (actual time=6,065.605..6,096.079 rows=390,912 loops=1)

  • Sort Key: signal."timestamp
  • Sort Method: quicksort Memory: 91797kB
  • Planning time: 2.300 ms
  • Execution time: 6165.522 ms
2.          

CTE source_ids

3. 0.012 0.024 ↑ 2.5 2 1

Nested Loop (cost=0.00..5.09 rows=5 width=4) (actual time=0.015..0.024 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.007 0.007 ↑ 1.0 1 1

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

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

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

6.          

Initplan (forSort)

7. 0.025 0.025 ↑ 2.5 2 1

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

8. 240.197 5,921.021 ↑ 3.4 390,912 1

GroupAggregate (cost=2,858,276.81..2,908,480.95 rows=1,338,777 width=167) (actual time=5,652.605..5,921.021 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. 223.390 5,680.824 ↑ 3.4 390,912 1

Sort (cost=2,858,276.81..2,861,623.76 rows=1,338,777 width=167) (actual time=5,652.594..5,680.824 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: quicksort Memory: 79151kB
10. 232.525 5,457.434 ↑ 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=5,121.333..5,457.434 rows=390,912 loops=1)

  • Join Filter: (quality.quality_id = signal_quality.quality_id)
  • Rows Removed by Join Filter: 1077609
11. 74.421 5,224.909 ↑ 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=5,121.304..5,224.909 rows=390,912 loops=1)

  • Merge Cond: (signal_quality.signal_quality_id = signals_signal_quality_association.signal_quality_id)
12. 0.019 0.019 ↑ 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.010..0.019 rows=4 loops=1)

13. 137.483 5,150.469 ↑ 3.4 390,912 1

Sort (cost=2,641,176.38..2,644,523.33 rows=1,338,777 width=107) (actual time=5,121.289..5,150.469 rows=390,912 loops=1)

  • Sort Key: signals_signal_quality_association.signal_quality_id
  • Sort Method: quicksort Memory: 71265kB
14. 355.804 5,012.986 ↑ 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=4,366.728..5,012.986 rows=390,912 loops=1)

  • Merge Cond: (signal.signal_id = signals_signal_quality_association.signal_id)
15. 61.549 961.012 ↑ 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=868.779..961.012 rows=390,912 loops=1)

  • Merge Cond: (signal.signal_id = signals_comments_association.signal_id)
16. 181.463 899.445 ↑ 3.4 390,912 1

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

  • Sort Key: signal.signal_id
  • Sort Method: quicksort Memory: 67261kB
17. 74.048 717.982 ↑ 3.4 390,912 1

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

  • Merge Cond: (source.source_id = signal.source_id)
18. 0.033 0.109 ↑ 1.4 61 1

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

  • Sort Key: source.source_id
  • Sort Method: quicksort Memory: 34kB
19. 0.020 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)
20. 0.010 0.018 ↓ 1.1 19 1

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

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

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

22. 0.021 0.038 ↑ 1.0 83 1

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

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

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

24. 112.247 643.825 ↑ 3.4 390,912 1

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

  • Sort Key: signal.source_id
  • Sort Method: quicksort Memory: 67261kB
25. 64.857 531.578 ↑ 3.4 390,912 1

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

  • Merge Cond: (site.site_id = signal.site_id)
26. 0.015 0.025 ↑ 1.2 40 1

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

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

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

28. 100.717 466.696 ↑ 3.4 390,912 1

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

  • Sort Key: signal.site_id
  • Sort Method: quicksort Memory: 53009kB
29. 61.231 365.979 ↑ 3.4 390,912 1

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

  • Merge Cond: (parameter.parameter_id = signal.parameter_id)
30. 0.016 0.026 ↓ 1.5 33 1

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

  • Sort Key: parameter.parameter_id
  • Sort Method: quicksort Memory: 28kB
31. 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)

32. 89.788 304.722 ↑ 6.1 390,912 1

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

  • Sort Key: signal.parameter_id
  • Sort Method: quicksort Memory: 42829kB
33. 160.804 214.934 ↑ 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=115.652..214.934 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
34. 54.130 54.130 ↑ 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=54.130..54.130 rows=938,746 loops=1)

  • Index Cond: (source_id = ANY ($1))
35. 0.016 0.018 ↓ 0.0 0 1

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

  • Sort Key: signals_comments_association.signal_id
  • Sort Method: quicksort Memory: 25kB
36. 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)

37. 3,284.414 3,696.170 ↑ 1.1 4,564,768 1

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

  • Sort Key: signals_signal_quality_association.signal_id
  • Sort Method: external sort Disk: 103384kB
38. 411.756 411.756 ↑ 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.107..411.756 rows=4,807,213 loops=1)

39. 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)

40. 0.011 0.011 ↑ 1.0 3 1

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