explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jcmu

Settings
# exclusive inclusive rows x rows loops node
1. 356.955 10,849.667 ↑ 1.5 390,912 1

Sort (cost=2,038,666.07..2,040,095.08 rows=571,603 width=167) (actual time=10,793.449..10,849.667 rows=390,912 loops=1)

  • Sort Key: signal."timestamp
  • Sort Method: external sort Disk: 47040kB
2. 249.111 10,492.712 ↑ 1.5 390,912 1

GroupAggregate (cost=1,915,682.91..1,937,118.02 rows=571,603 width=167) (actual time=10,103.544..10,492.712 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. 477.891 10,243.601 ↑ 1.5 390,912 1

Sort (cost=1,915,682.91..1,917,111.91 rows=571,603 width=167) (actual time=10,103.534..10,243.601 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. 60.256 9,765.710 ↑ 1.5 390,912 1

Hash Left Join (cost=1,656,693.78..1,814,134.86 rows=571,603 width=167) (actual time=8,181.697..9,765.710 rows=390,912 loops=1)

  • Hash Cond: (signal_quality.quality_id = quality.quality_id)
5. 63.137 9,705.442 ↑ 1.5 390,912 1

Hash Left Join (cost=1,656,664.65..1,806,246.19 rows=571,603 width=107) (actual time=8,181.667..9,705.442 rows=390,912 loops=1)

  • Hash Cond: (signals_signal_quality_association.signal_quality_id = signal_quality.signal_quality_id)
6. 86.370 9,636.000 ↑ 1.5 390,912 1

Hash Left Join (cost=1,656,082.93..1,797,804.93 rows=571,603 width=107) (actual time=8,175.216..9,636.000 rows=390,912 loops=1)

  • Hash Cond: (signal.site_id = site.site_id)
7. 1,078.600 9,549.600 ↑ 1.5 390,912 1

Hash Right Join (cost=1,656,079.85..1,789,942.31 rows=571,603 width=95) (actual time=8,175.157..9,549.600 rows=390,912 loops=1)

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

Seq Scan on signals_signal_quality_association (cost=0.00..69,528.42 rows=4,806,242 width=8) (actual time=0.023..394.347 rows=4,807,213 loops=1)

9. 112.097 8,076.653 ↑ 1.5 390,912 1

Hash (cost=1,640,560.81..1,640,560.81 rows=571,603 width=91) (actual time=8,076.653..8,076.653 rows=390,912 loops=1)

  • Buckets: 32768 Batches: 32 Memory Usage: 1713kB
10. 104.674 7,964.556 ↑ 1.5 390,912 1

Hash Right Join (cost=1,632,686.56..1,640,560.81 rows=571,603 width=91) (actual time=7,860.004..7,964.556 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. 129.251 7,859.881 ↑ 1.5 390,912 1

Hash (cost=1,617,726.52..1,617,726.52 rows=571,603 width=87) (actual time=7,859.881..7,859.881 rows=390,912 loops=1)

  • Buckets: 32768 Batches: 32 Memory Usage: 1619kB
13. 90.349 7,730.630 ↑ 1.5 390,912 1

Hash Join (cost=442,344.42..1,617,726.52 rows=571,603 width=87) (actual time=2,318.672..7,730.630 rows=390,912 loops=1)

  • Hash Cond: (signal.parameter_id = parameter.parameter_id)
14. 1,735.208 7,640.259 ↑ 2.6 390,912 1

Hash Join (cost=442,342.93..1,608,209.14 rows=1,013,296 width=76) (actual time=2,318.636..7,640.259 rows=390,912 loops=1)

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

Bitmap Heap Scan on signal (cost=442,338.60..1,521,380.64 rows=20,488,003 width=32) (actual time=2,309.016..5,905.007 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,302.643 2,302.643 ↑ 1.0 20,459,097 1

Bitmap Index Scan on ix_signal_timestamp (cost=0.00..437,216.60 rows=20,488,003 width=0) (actual time=2,302.643..2,302.643 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.044 ↑ 2.0 2 1

Hash (cost=4.28..4.28 rows=4 width=52) (actual time=0.044..0.044 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.019 0.041 ↑ 2.0 2 1

Hash Join (cost=1.24..4.28 rows=4 width=52) (actual time=0.033..0.041 rows=2 loops=1)

  • Hash Cond: (source.source_type_id = source_type.source_type_id)
19. 0.008 0.008 ↓ 1.1 83 1

Seq Scan on source (cost=0.00..2.73 rows=73 width=38) (actual time=0.002..0.008 rows=83 loops=1)

20. 0.005 0.014 ↑ 1.0 1 1

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

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

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

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

Hash (cost=1.22..1.22 rows=22 width=19) (actual time=0.022..0.022 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.004..0.009 rows=46 loops=1)

24. 0.012 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.018 0.018 ↑ 1.0 48 1

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

26. 3.206 6.305 ↓ 1.0 20,145 1

Hash (cost=330.21..330.21 rows=20,121 width=8) (actual time=6.305..6.305 rows=20,145 loops=1)

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

Seq Scan on signal_quality (cost=0.00..330.21 rows=20,121 width=8) (actual time=0.011..3.099 rows=20,145 loops=1)

28. 0.003 0.012 ↑ 283.3 3 1

Hash (cost=18.50..18.50 rows=850 width=68) (actual time=0.012..0.012 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.009 0.009 ↑ 283.3 3 1

Seq Scan on quality (cost=0.00..18.50 rows=850 width=68) (actual time=0.008..0.009 rows=3 loops=1)