explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9YCr

Settings
# exclusive inclusive rows x rows loops node
1. 355.171 7,838.352 ↑ 3.4 390,912 1

Sort (cost=3,944,712.47..3,948,059.41 rows=1,338,777 width=167) (actual time=7,780.034..7,838.352 rows=390,912 loops=1)

  • Sort Key: signal."timestamp
  • Sort Method: external sort Disk: 47040kB
  • Planning time: 2.211 ms
  • Execution time: 7927.464 ms
2.          

CTE source_ids

3. 0.016 0.050 ↑ 2.5 2 1

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

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

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

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

6.          

Initplan (forSort)

7. 0.053 0.053 ↑ 2.5 2 1

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

8. 248.582 7,483.128 ↑ 3.4 390,912 1

GroupAggregate (cost=3,538,619.95..3,588,824.08 rows=1,338,777 width=167) (actual time=7,101.228..7,483.128 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. 474.725 7,234.546 ↑ 3.4 390,912 1

Sort (cost=3,538,619.95..3,541,966.89 rows=1,338,777 width=167) (actual time=7,101.215..7,234.546 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
10. 252.704 6,759.821 ↑ 3.4 390,912 1

Nested Loop Left Join (cost=2,875,288.33..3,182,736.76 rows=1,338,777 width=167) (actual time=5,857.776..6,759.821 rows=390,912 loops=1)

  • Join Filter: (quality.quality_id = signal_quality.quality_id)
  • Rows Removed by Join Filter: 1077609
11. 13.351 6,507.117 ↑ 3.4 390,912 1

Nested Loop Left Join (cost=2,875,288.33..3,122,490.75 rows=1,338,777 width=107) (actual time=5,857.758..6,507.117 rows=390,912 loops=1)

12. 77.199 6,102.854 ↑ 3.4 390,912 1

Merge Join (cost=2,875,288.19..2,898,236.58 rows=1,338,777 width=95) (actual time=5,857.733..6,102.854 rows=390,912 loops=1)

  • Merge Cond: (source.source_id = signal.source_id)
13. 0.042 0.120 ↑ 1.4 61 1

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

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

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

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

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

  • Sort Key: source_type.source_type_id
  • Sort Method: quicksort Memory: 26kB
16. 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.005..0.008 rows=19 loops=1)

17. 0.027 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
18. 0.014 0.014 ↑ 1.0 83 1

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

19. 66.195 6,025.535 ↑ 3.4 390,912 1

Materialize (cost=2,875,277.33..2,881,971.21 rows=1,338,777 width=51) (actual time=5,857.613..6,025.535 rows=390,912 loops=1)

20. 282.937 5,959.340 ↑ 3.4 390,912 1

Sort (cost=2,875,277.33..2,878,624.27 rows=1,338,777 width=51) (actual time=5,857.611..5,959.340 rows=390,912 loops=1)

  • Sort Key: signal.source_id
  • Sort Method: external merge Disk: 25672kB
21. 72.693 5,676.403 ↑ 3.4 390,912 1

Merge Right Join (cost=2,669,317.24..2,693,281.14 rows=1,338,777 width=51) (actual time=5,453.092..5,676.403 rows=390,912 loops=1)

  • Merge Cond: (signal_quality.signal_quality_id = signals_signal_quality_association.signal_quality_id)
22. 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)

23. 64.680 5,603.691 ↑ 3.4 390,912 1

Materialize (cost=2,669,316.96..2,676,010.84 rows=1,338,777 width=51) (actual time=5,453.079..5,603.691 rows=390,912 loops=1)

24. 248.486 5,539.011 ↑ 3.4 390,912 1

Sort (cost=2,669,316.96..2,672,663.90 rows=1,338,777 width=51) (actual time=5,453.077..5,539.011 rows=390,912 loops=1)

  • Sort Key: signals_signal_quality_association.signal_quality_id
  • Sort Method: external merge Disk: 25680kB
25. 379.331 5,290.525 ↑ 3.4 390,912 1

Merge Right Join (cost=2,449,458.68..2,487,320.77 rows=1,338,777 width=51) (actual time=4,270.578..5,290.525 rows=390,912 loops=1)

  • Merge Cond: (signals_signal_quality_association.signal_id = signal.signal_id)
26. 3,512.340 3,899.885 ↑ 1.1 4,564,768 1

Sort (cost=734,514.12..746,532.15 rows=4,807,213 width=8) (actual time=2,971.762..3,899.885 rows=4,564,768 loops=1)

  • Sort Key: signals_signal_quality_association.signal_id
  • Sort Method: external merge Disk: 84552kB
27. 387.545 387.545 ↑ 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.010..387.545 rows=4,807,213 loops=1)

28. 69.927 1,011.309 ↑ 3.4 390,912 1

Materialize (cost=1,714,944.56..1,724,997.11 rows=1,338,777 width=47) (actual time=833.125..1,011.309 rows=390,912 loops=1)

29. 57.102 941.382 ↑ 3.4 390,912 1

Merge Left Join (cost=1,714,944.56..1,721,650.17 rows=1,338,777 width=47) (actual time=833.117..941.382 rows=390,912 loops=1)

  • Merge Cond: (signal.signal_id = signals_comments_association.signal_id)
30. 278.225 884.266 ↑ 3.4 390,912 1

Sort (cost=1,714,786.06..1,718,133.00 rows=1,338,777 width=43) (actual time=833.099..884.266 rows=390,912 loops=1)

  • Sort Key: signal.signal_id
  • Sort Method: external sort Disk: 22136kB
31. 15.529 606.041 ↑ 3.4 390,912 1

Nested Loop (cost=147,684.08..1,537,364.37 rows=1,338,777 width=43) (actual time=114.021..606.041 rows=390,912 loops=1)

32. 146.137 199.600 ↑ 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=114.002..199.600 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.463 53.463 ↑ 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.463..53.463 rows=938,746 loops=1)

  • Index Cond: (source_id = ANY ($1))
34. 390.912 390.912 ↑ 1.0 1 390,912

Index Scan using parameter_pkey on parameter (cost=0.14..0.16 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=390,912)

  • Index Cond: (parameter_id = signal.parameter_id)
35. 0.013 0.014 ↓ 0.0 0 1

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

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

37. 390.912 390.912 ↑ 1.0 1 390,912

Index Scan using ix_site_site_id on site (cost=0.14..0.16 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=390,912)

  • Index Cond: (signal.site_id = site_id)
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.004 0.004 ↑ 1.0 3 1

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