explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xsvw

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 2,846.908 ↓ 0.0 0 1

HashAggregate (cost=753.54..755.54 rows=200 width=20) (actual time=2,846.908..2,846.908 rows=0 loops=1)

  • Group Key: bgd.gaugesummaryid
2. 0.017 2,846.906 ↓ 0.0 0 1

Nested Loop (cost=451.02..666.04 rows=5,000 width=32) (actual time=2,846.906..2,846.906 rows=0 loops=1)

3.          

CTE foo

4. 0.008 2.006 ↓ 10.0 10 1

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

5. 0.015 1.998 ↓ 10.0 10 1

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

6. 0.847 1.923 ↑ 2.8 5 1

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

  • Hash Cond: (gsr.gaugesummaryid = gs.gaugesummaryid)
7. 0.610 0.610 ↑ 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.610 rows=10,429 loops=1)

8. 0.040 0.466 ↓ 55.0 275 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
9. 0.132 0.426 ↓ 55.0 275 1

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

10. 0.032 0.084 ↓ 105.0 210 1

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

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

BitmapAnd (cost=55.12..55.12 rows=2 width=0) (actual time=0.052..0.052 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.031 0.031 ↑ 6.5 457 1

Bitmap Index Scan on geometrypointdata_idx1 (cost=0.00..33.71 rows=2,971 width=0) (actual time=0.031..0.031 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.060 0.060 ↓ 2.0 2 5

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

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

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

17. 0.040 2,844.870 ↓ 0.0 0 10

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

  • Merge Cond: ((((rgd.unixdatetime / 300) * 300)) = (((bgd.unixdatetime / 300) * 300)))
18. 8.130 2,843.050 ↑ 1,000.0 1 10

Sort (cost=60.08..62.58 rows=1,000 width=12) (actual time=284.305..284.305 rows=1 loops=10)

  • Sort Key: (((rgd.unixdatetime / 300) * 300))
  • Sort Method: quicksort Memory: 784kB
19. 2,834.920 2,834.920 ↓ 8.5 8,526 10

Function Scan on gaugedata_getgroupeddata rgd (cost=0.25..10.25 rows=1,000 width=12) (actual time=282.361..283.492 rows=8,526 loops=10)

20. 0.060 1.780 ↓ 0.0 0 10

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

  • Sort Key: (((bgd.unixdatetime / 300) * 300))
  • Sort Method: quicksort Memory: 25kB
21. 1.720 1.720 ↓ 0.0 0 10

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