explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 51.311 2,548.229 ↓ 7.3 111,607 1

Hash Left Join (cost=207,169.37..574,025.48 rows=15,386 width=222) (actual time=960.226..2,548.229 rows=111,607 loops=1)

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

Merge Left Join (cost=207,164.62..573,715.33 rows=15,386 width=159) (actual time=960.129..2,496.837 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. 106.194 2,387.188 ↓ 7.3 111,607 1

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

4. 227.547 1,164.924 ↓ 1.2 111,607 1

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

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

Sort (cost=203,041.73..203,283.91 rows=96,872 width=53) (actual time=911.295..937.377 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. 134.376 134.376 ↓ 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.079..134.376 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,116.070 1,116.070 ↑ 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.010..0.010 rows=1 loops=111,607)

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

Sort (cost=4,122.47..4,122.47 rows=1 width=68) (actual time=48.758..49.450 rows=4,657 loops=1)

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

Hash Join (cost=2,932.52..4,122.45 rows=1 width=68) (actual time=31.137..46.090 rows=1,497 loops=1)

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

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

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

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

12. 0.495 14.324 ↓ 9.1 2,126 1

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

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

Seq Scan on block_history b (cost=0.00..1,464.51 rows=234 width=85) (actual time=0.335..13.829 rows=2,126 loops=1)

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

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

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

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

Planning time : 1.860 ms
Execution time : 2,559.695 ms