explain.depesz.com

PostgreSQL's explain analyze made readable

Result: deTh : Optimization for: plan #fh1o

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4,141.149 10,500.164 ↑ 1.0 6,000,479 1

Sort (cost=2,050,279.61..2,065,435.30 rows=6,062,275 width=127) (actual time=9,752.837..10,500.164 rows=6,000,479 loops=1)

  • Sort Key: (COALESCE(date_part('day'::text, (now() - b.ts)), '2147483647'::double precision)) DESC
  • Sort Method: external sort Disk: 340600kB
2. 5,469.437 6,359.015 ↑ 1.0 6,000,479 1

Hash Left Join (cost=4,122.47..186,239.05 rows=6,062,275 width=127) (actual time=42.890..6,359.015 rows=6,000,479 loops=1)

  • Hash Cond: (concat(s.ts_id, '-', s.widget_id) = (b.key)::text)
3. 846.775 846.775 ↑ 1.0 6,000,479 1

Seq Scan on widget_status s (cost=0.00..106,336.75 rows=6,062,275 width=30) (actual time=0.039..846.775 rows=6,000,479 loops=1)

4. 0.470 42.803 ↓ 1,497.0 1,497 1

Hash (cost=4,122.45..4,122.45 rows=1 width=89) (actual time=42.803..42.803 rows=1,497 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 194kB
5. 5.717 42.333 ↓ 1,497.0 1,497 1

Hash Join (cost=2,932.52..4,122.45 rows=1 width=89) (actual time=27.935..42.333 rows=1,497 loops=1)

  • Hash Cond: (((block_history.key)::text = (b.key)::text) AND ((max(block_history.seq)) = b.seq))
6. 22.166 25.535 ↑ 1.0 41,764 1

HashAggregate (cost=1,464.51..1,897.21 rows=43,270 width=17) (actual time=16.820..25.535 rows=41,764 loops=1)

  • Group Key: block_history.key
7. 3.369 3.369 ↑ 1.1 44,254 1

Seq Scan on block_history (cost=0.00..1,230.67 rows=46,767 width=17) (actual time=0.016..3.369 rows=44,254 loops=1)

8. 0.580 11.081 ↓ 9.1 2,126 1

Hash (cost=1,464.51..1,464.51 rows=234 width=93) (actual time=11.081..11.081 rows=2,126 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 297kB
9. 10.501 10.501 ↓ 9.1 2,126 1

Seq Scan on block_history b (cost=0.00..1,464.51 rows=234 width=93) (actual time=0.280..10.501 rows=2,126 loops=1)

  • Filter: ((value ->> 'blocked'::text) = 'false'::text)
  • Rows Removed by Filter: 42128
Planning time : 0.673 ms
Execution time : 10,696.338 ms