explain.depesz.com

PostgreSQL's explain analyze made readable

Result: atmM

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 2.608 ↓ 0.0 0 1

HashAggregate (cost=693.54..695.54 rows=200 width=20) (actual time=2.608..2.608 rows=0 loops=1)

  • Group Key: bgd.gaugesummaryid
2. 0.010 2.607 ↓ 0.0 0 1

Nested Loop (cost=451.02..606.04 rows=5,000 width=32) (actual time=2.607..2.607 rows=0 loops=1)

3.          

CTE foo

4. 0.006 1.969 ↓ 10.0 10 1

Limit (cost=66.50..330.86 rows=1 width=16) (actual time=1.947..1.969 rows=10 loops=1)

5. 0.003 1.963 ↓ 10.0 10 1

Nested Loop (cost=66.50..330.86 rows=1 width=16) (actual time=1.946..1.963 rows=10 loops=1)

6. 0.870 1.940 ↑ 2.8 5 1

Hash Semi Join (cost=66.22..326.45 rows=14 width=8) (actual time=1.936..1.940 rows=5 loops=1)

  • Hash Cond: (gsr.gaugesummaryid = gs.gaugesummaryid)
7. 0.627 0.627 ↑ 1.1 10,429 1

Seq Scan on gaugesummaryrelationship gsr (cost=0.00..229.29 rows=11,729 width=8) (actual time=0.004..0.627 rows=10,429 loops=1)

8. 0.024 0.443 ↓ 55.0 275 1

Hash (cost=66.15..66.15 rows=5 width=4) (actual time=0.443..0.443 rows=275 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
9. 0.133 0.419 ↓ 55.0 275 1

Nested Loop (cost=55.54..66.15 rows=5 width=4) (actual time=0.068..0.419 rows=275 loops=1)

10. 0.026 0.076 ↓ 105.0 210 1

Bitmap Heap Scan on geometrypointdata (cost=55.12..57.15 rows=2 width=4) (actual time=0.056..0.076 rows=210 loops=1)

  • Recheck Cond: ((assettypeid = 3533) AND (workid = 961))
  • Heap Blocks: exact=5
11. 0.001 0.050 ↓ 0.0 0 1

BitmapAnd (cost=55.12..55.12 rows=2 width=0) (actual time=0.050..0.050 rows=0 loops=1)

12. 0.020 0.020 ↑ 8.7 210 1

Bitmap Index Scan on geometrypointdata_idx2 (cost=0.00..21.16 rows=1,830 width=0) (actual time=0.020..0.020 rows=210 loops=1)

  • Index Cond: (assettypeid = 3533)
13. 0.029 0.029 ↑ 6.5 457 1

Bitmap Index Scan on geometrypointdata_idx1 (cost=0.00..33.71 rows=2,971 width=0) (actual time=0.029..0.029 rows=457 loops=1)

  • Index Cond: (workid = 961)
14. 0.210 0.210 ↑ 3.0 1 210

Index Scan using gaugesummary_idx on gaugesummary gs (cost=0.42..4.47 rows=3 width=8) (actual time=0.001..0.001 rows=1 loops=210)

  • Index Cond: (assetid = geometrypointdata.assetid)
  • Filter: (telemeteredmaximumtime IS NOT NULL)
15. 0.020 0.020 ↓ 2.0 2 5

Index Scan using datavariablethreshold_idx1 on datavariablethreshold dvt (cost=0.28..0.31 rows=1 width=12) (actual time=0.003..0.004 rows=2 loops=5)

  • Index Cond: (gaugesummaryid = gsr.relatedgaugesummaryid)
  • Filter: (thresholdcategory = 1)
  • Rows Removed by Filter: 1
16. 1.977 1.977 ↓ 10.0 10 1

CTE Scan on foo (cost=0.00..0.02 rows=1 width=16) (actual time=1.949..1.977 rows=10 loops=1)

17. 0.000 0.620 ↓ 0.0 0 10

Merge Join (cost=120.16..200.16 rows=5,000 width=12) (actual time=0.062..0.062 rows=0 loops=10)

  • Merge Cond: (bgd.unixdatetime = rgd.unixdatetime)
18. 0.010 0.620 ↓ 0.0 0 10

Sort (cost=60.08..62.58 rows=1,000 width=8) (actual time=0.062..0.062 rows=0 loops=10)

  • Sort Key: bgd.unixdatetime
  • Sort Method: quicksort Memory: 25kB
19. 0.610 0.610 ↓ 0.0 0 10

Function Scan on gaugedata_getgroupeddata bgd (cost=0.25..10.25 rows=1,000 width=8) (actual time=0.061..0.061 rows=0 loops=10)

20. 0.000 0.000 ↓ 0.0 0

Sort (cost=60.08..62.58 rows=1,000 width=12) (never executed)

  • Sort Key: rgd.unixdatetime
21. 0.000 0.000 ↓ 0.0 0

Function Scan on gaugedata_getgroupeddata rgd (cost=0.25..10.25 rows=1,000 width=12) (never executed)