explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZpF

Settings
# exclusive inclusive rows x rows loops node
1. 3.029 79.603 ↓ 149.5 299 1

Hash Full Join (cost=1,706.52..1,877.85 rows=2 width=100) (actual time=70.405..79.603 rows=299 loops=1)

  • Hash Cond: (block1_cte.bidx = block2_cte.bidx)
  • Filter: ((block1_cte.bidx IS NULL) OR (block2_cte.bidx IS NULL) OR ((block2_cte.block_chksum)::text <> (block1_cte.block_chksum)::text))
  • Rows Removed by Filter: 6,394
2.          

CTE block1_cte

3. 3.325 3.325 ↑ 1.1 7,189 1

Seq Scan on block_seek_pos (cost=0.00..597.42 rows=7,607 width=197) (actual time=0.057..3.325 rows=7,189 loops=1)

  • Filter: (fbid = 1)
  • Rows Removed by Filter: 6,765
4.          

CTE block2_cte

5. 5.739 5.739 ↓ 1.1 6,765 1

Seq Scan on block_seek_pos block_seek_pos_1 (cost=0.00..597.42 rows=6,347 width=197) (actual time=1.981..5.739 rows=6,765 loops=1)

  • Filter: (fbid = 2)
  • Rows Removed by Filter: 7,189
6. 3.932 45.496 ↓ 6,692.0 6,692 1

Hash Join (cost=209.39..380.70 rows=1 width=50) (actual time=39.305..45.496 rows=6,692 loops=1)

  • Hash Cond: (block1_cte.sbid = t1.sbid)
7. 2.354 2.354 ↓ 188.7 7,169 1

CTE Scan on block1_cte (cost=0.00..171.16 rows=38 width=50) (actual time=0.062..2.354 rows=7,169 loops=1)

  • Filter: ((fguid)::text = 'ac15c785776ab44bd58a60f99a240515'::text)
  • Rows Removed by Filter: 20
8. 1.719 39.210 ↓ 1,673.0 6,692 1

Hash (cost=209.34..209.34 rows=4 width=8) (actual time=39.209..39.210 rows=6,692 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 326kB
9. 0.823 37.491 ↓ 1,673.0 6,692 1

Subquery Scan on t1 (cost=209.23..209.34 rows=4 width=8) (actual time=32.295..37.491 rows=6,692 loops=1)

10. 3.847 36.668 ↓ 1,673.0 6,692 1

GroupAggregate (cost=209.23..209.30 rows=4 width=16) (actual time=32.294..36.668 rows=6,692 loops=1)

  • Group Key: block1_cte_1.bidx
11. 3.071 32.821 ↓ 1,718.0 6,872 1

Sort (cost=209.23..209.24 rows=4 width=16) (actual time=32.281..32.821 rows=6,872 loops=1)

  • Sort Key: block1_cte_1.bidx
  • Sort Method: quicksort Memory: 515kB
12. 29.750 29.750 ↓ 1,718.0 6,872 1

CTE Scan on block1_cte block1_cte_1 (cost=0.00..209.19 rows=4 width=16) (actual time=0.004..29.750 rows=6,872 loops=1)

  • Filter: ((sbid > 0) AND (schid <= 3) AND ((fguid)::text = 'ac15c785776ab44bd58a60f99a240515'::text))
  • Rows Removed by Filter: 317
13. 1.809 31.078 ↓ 3,346.0 6,692 1

Hash (cost=302.25..302.25 rows=2 width=50) (actual time=31.078..31.078 rows=6,692 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 594kB
14. 4.295 29.269 ↓ 3,346.0 6,692 1

Hash Join (cost=159.31..302.25 rows=2 width=50) (actual time=22.551..29.269 rows=6,692 loops=1)

  • Hash Cond: (block2_cte.sbid = t1_1.sbid)
15. 4.434 4.434 ↓ 211.4 6,765 1

CTE Scan on block2_cte (cost=0.00..142.81 rows=32 width=50) (actual time=1.988..4.434 rows=6,765 loops=1)

  • Filter: ((fguid)::text = '5303ee30dacdde734dec68ede9346d8f'::text)
16. 1.027 20.540 ↓ 608.4 6,692 1

Hash (cost=159.17..159.17 rows=11 width=8) (actual time=20.540..20.540 rows=6,692 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 326kB
17. 0.616 19.513 ↓ 608.4 6,692 1

Subquery Scan on t1_1 (cost=158.87..159.17 rows=11 width=8) (actual time=16.050..19.513 rows=6,692 loops=1)

18. 2.499 18.897 ↓ 608.4 6,692 1

GroupAggregate (cost=158.87..159.06 rows=11 width=16) (actual time=16.048..18.897 rows=6,692 loops=1)

  • Group Key: block2_cte_1.bidx
19. 3.741 16.398 ↓ 614.8 6,763 1

Sort (cost=158.87..158.89 rows=11 width=16) (actual time=16.037..16.398 rows=6,763 loops=1)

  • Sort Key: block2_cte_1.bidx
  • Sort Method: quicksort Memory: 510kB
20. 12.657 12.657 ↓ 614.8 6,763 1

CTE Scan on block2_cte block2_cte_1 (cost=0.00..158.68 rows=11 width=16) (actual time=0.005..12.657 rows=6,763 loops=1)

  • Filter: ((sbid > 0) AND ((fguid)::text = '5303ee30dacdde734dec68ede9346d8f'::text))
  • Rows Removed by Filter: 2
Planning time : 0.868 ms
Execution time : 81.548 ms