explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZKYD : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #fh1o; plan #deTh; plan #7vTb; plan #I063; plan #DcAv; plan #HCvZ; plan #wady

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 70.471 2,621.848 ↓ 7.3 111,607 1

Hash Left Join (cost=207,169.37..574,140.88 rows=15,386 width=230) (actual time=967.236..2,621.848 rows=111,607 loops=1)

  • Hash Cond: ((widgets.widget_id)::text = (ps.widget_id)::text)
2. 65.527 2,551.293 ↓ 7.3 111,607 1

Merge Left Join (cost=207,164.62..573,715.33 rows=15,386 width=167) (actual time=967.133..2,551.293 rows=111,607 loops=1)

  • Merge Cond: ((widget_status.widget_id)::text = ((b.value ->> 'widget_id'::text)))
  • Join Filter: (widget_status.ts_id = (COALESCE((b.value ->> 'boost_id'::text), '0'::text))::integer)
  • Rows Removed by Join Filter: 4320
3. 37.531 2,435.445 ↓ 7.3 111,607 1

Nested Loop (cost=203,042.16..569,515.91 rows=15,386 width=168) (actual time=917.547..2,435.445 rows=111,607 loops=1)

4. 226.170 1,170.237 ↓ 1.2 111,607 1

GroupAggregate (cost=203,041.73..218,015.04 rows=96,332 width=158) (actual time=917.503..1,170.237 rows=111,607 loops=1)

  • Group Key: widgets.widget_id, widgets.imb_id, widgets.source_id, widgets.ts_id
5. 811.290 944.067 ↓ 1.2 111,607 1

Sort (cost=203,041.73..203,283.91 rows=96,872 width=53) (actual time=917.445..944.067 rows=111,607 loops=1)

  • Sort Key: widgets.widget_id, widgets.imb_id, widgets.source_id, widgets.ts_id
  • Sort Method: external sort Disk: 5912kB
6. 132.777 132.777 ↓ 1.2 111,607 1

Index Scan using widgets_pkey on widgets (cost=0.57..191,704.39 rows=96,872 width=53) (actual time=0.072..132.777 rows=111,607 loops=1)

  • Index Cond: (date = (now())::date)
  • Filter: ((ts_id IS NOT NULL) AND (date <> '2018-07-18'::date))
  • Rows Removed by Filter: 113
7. 1,227.677 1,227.677 ↑ 1.0 1 111,607

Index Scan using widget_status_pkey on widget_status (cost=0.43..3.63 rows=1 width=10) (actual time=0.011..0.011 rows=1 loops=111,607)

  • Index Cond: (((widget_id)::text = (widgets.widget_id)::text) AND (ts_id = widgets.ts_id))
8. 3.332 50.321 ↓ 4,657.0 4,657 1

Sort (cost=4,122.47..4,122.47 rows=1 width=76) (actual time=49.582..50.321 rows=4,657 loops=1)

  • Sort Key: ((b.value ->> 'widget_id'::text))
  • Sort Method: quicksort Memory: 264kB
9. 6.116 46.989 ↓ 1,497.0 1,497 1

Hash Join (cost=2,932.52..4,122.45 rows=1 width=76) (actual time=31.361..46.989 rows=1,497 loops=1)

  • Hash Cond: (((block_history.key)::text = (b.key)::text) AND ((max(block_history.seq)) = b.seq))
10. 23.681 27.388 ↑ 1.0 41,764 1

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

  • Group Key: block_history.key
11. 3.707 3.707 ↑ 1.1 44,254 1

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

12. 0.487 13.485 ↓ 9.1 2,126 1

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

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 299kB
13. 12.998 12.998 ↓ 9.1 2,126 1

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

  • Filter: ((value ->> 'blocked'::text) = 'false'::text)
  • Rows Removed by Filter: 42128
14. 0.033 0.084 ↑ 1.0 122 1

Hash (cost=3.22..3.22 rows=122 width=548) (actual time=0.084..0.084 rows=122 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
15. 0.051 0.051 ↑ 1.0 122 1

Seq Scan on widgets_profit_status ps (cost=0.00..3.22 rows=122 width=548) (actual time=0.035..0.051 rows=122 loops=1)