explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4dOL

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

ort (cost=3,944,712.47..3,948,059.41 rows=1,338,777 width=167) (actual time=7,699.426..7,756.028 rows=390,912 loops=1)

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

CTE source_ids

3. 0.016 0.049 ↑ 2.5 2 1

Nested Loop (cost=0.00..5.09 rows=5 width=4) (actual time=0.038..0.049 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.023 0.023 ↑ 1.0 1 1

Seq Scan on source_type source_type_1 (cost=0.00..1.23 rows=1 width=4) (actual time=0.022..0.023 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.003..0.010 rows=83 loops=1)

6.          

Initplan (forort)

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.041..0.053 rows=2 loops=1)

8. 249.701 7,407.001 ↑ 3.4 390,912 1

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

Sort (cost=3,538,619.95..3,541,966.89 rows=1,338,777 width=167) (actual time=7,024.402..7,157.300 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. 251.154 6,688.346 ↑ 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,786.924..6,688.346 rows=390,912 loops=1)

  • Join Filter: (quality.quality_id = signal_quality.quality_id)
  • Rows Removed by Join Filter: 1077609
11. 11.539 6,437.192 ↑ 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,786.906..6,437.192 rows=390,912 loops=1)

12. 76.921 6,034.741 ↑ 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,786.882..6,034.741 rows=390,912 loops=1)

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

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

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

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

  • Merge Cond: (source_type.source_type_id = source.source_type_id)
15. 0.012 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
16. 0.005 0.005 ↓ 1.1 19 1

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

17. 0.027 0.042 ↑ 1.0 83 1

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

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

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

19. 65.137 5,957.704 ↑ 3.4 390,912 1

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

20. 274.966 5,892.567 ↑ 3.4 390,912 1

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

  • Sort Key: signal.source_id
  • Sort Method: external merge Disk: 25672kB
21. 70.112 5,617.601 ↑ 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,404.489..5,617.601 rows=390,912 loops=1)

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

23. 61.628 5,547.472 ↑ 3.4 390,912 1

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

24. 234.693 5,485.844 ↑ 3.4 390,912 1

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

  • Sort Key: signals_signal_quality_association.signal_quality_id
  • Sort Method: external merge Disk: 25680kB
25. 363.143 5,251.151 ↑ 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,282.393..5,251.151 rows=390,912 loops=1)

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

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

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

28. 67.451 1,007.862 ↑ 3.4 390,912 1

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

29. 54.876 940.411 ↑ 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=836.652..940.411 rows=390,912 loops=1)

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

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

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

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

32. 143.586 196.575 ↑ 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=112.368..196.575 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. 52.989 52.989 ↑ 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=52.989..52.989 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)