explain.depesz.com

PostgreSQL's explain analyze made readable

Result: exZ7

Settings
# exclusive inclusive rows x rows loops node
1. 0.562 186,192.369 ↑ 199.0 1 1

Unique (cost=2,740,403.51..2,740,409.98 rows=199 width=168) (actual time=186,192.367..186,192.369 rows=1 loops=1)

2. 0.000 186,191.807 ↑ 199.0 1 1

Sort (cost=2,740,403.51..2,740,404.01 rows=199 width=168) (actual time=186,191.806..186,191.807 rows=1 loops=1)

  • Sort Key: i.instrument_type_id, i.tid, i.name, (COALESCE(p.price, 0.0)), (COALESCE(p.last_progname, 'MISSING'::character varying)), i.underlying_tid, i.master_tid, i.price_base, m.price_table_prefix, ((SubPlan 1)), i.currency_code, i.death_date
  • Sort Method: quicksort Memory: 25kB
3. 36.980 186,197.222 ↑ 199.0 1 1

Gather (cost=392,444.79..2,740,395.91 rows=199 width=168) (actual time=6,516.716..186,197.222 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.338 186,160.215 ↓ 0.0 0 3 / 3

Hash Left Join (cost=391,444.79..2,737,750.68 rows=83 width=99) (actual time=126,277.012..186,160.215 rows=0 loops=3)

  • Hash Cond: (i.primary_market_id = m.market_id)
5. 0.163 186,159.114 ↓ 0.0 0 3 / 3

Nested Loop Left Join (cost=391,413.93..2,737,719.60 rows=83 width=93) (actual time=126,276.241..186,159.114 rows=0 loops=3)

6. 1,996.509 186,158.943 ↓ 0.0 0 3 / 3

Hash Join (cost=391,413.50..2,737,038.56 rows=83 width=77) (actual time=126,276.228..186,158.943 rows=0 loops=3)

  • Hash Cond: (i.tid = o.ins_iid)
7. 182,593.154 184,161.715 ↓ 1.6 6,275,996 3 / 3

Parallel Bitmap Heap Scan on instrument i (cost=391,360.75..2,491,513.05 rows=3,927,551 width=73) (actual time=4,715.791..184,161.715 rows=6,275,996 loops=3)

  • Recheck Cond: (instrument_type_id = ANY ('{37,48,54,58}'::integer[]))
  • Rows Removed by Index Recheck: 2,776,620
  • Filter: (NOT (hashed SubPlan 2))
  • Heap Blocks: exact=18,619 lossy=221,958
8. 1,564.357 1,564.357 ↓ 1.1 19,927,082 1 / 3

Bitmap Index Scan on instrument_ind5 (cost=0.00..386,854.10 rows=18,852,246 width=0) (actual time=4,693.072..4,693.072 rows=19,927,082 loops=1)

  • Index Cond: (instrument_type_id = ANY ('{37,48,54,58}'::integer[]))
9.          

SubPlan (for Parallel Bitmap Heap Scan)

10. 4.204 4.204 ↑ 36.9 20 3 / 3

Index Only Scan using tp_options_ind1 on tp_options yesterday (cost=0.43..2,148.28 rows=737 width=4) (actual time=4.199..4.204 rows=20 loops=3)

  • Index Cond: (aex_date = to_date('2020-07-25'::text, 'YYYY-MM-DD'::text))
  • Heap Fetches: 20
11. 0.386 0.719 ↑ 1.0 1,633 3 / 3

Hash (cost=32.33..32.33 rows=1,633 width=4) (actual time=0.719..0.719 rows=1,633 loops=3)

  • Buckets: 2,048 Batches: 1 Memory Usage: 74kB
12. 0.333 0.333 ↑ 1.0 1,633 3 / 3

Seq Scan on openpos o (cost=0.00..32.33 rows=1,633 width=4) (actual time=0.012..0.333 rows=1,633 loops=3)

13. 0.008 0.008 ↓ 0.0 0 1 / 3

Index Scan using tp_options_ind1 on tp_options p (cost=0.43..8.20 rows=1 width=24) (actual time=0.023..0.023 rows=0 loops=1)

  • Index Cond: ((aex_date = to_date('2020-07-26'::text, 'YYYY-MM-DD'::text)) AND (o.ins_iid = iid))
14. 0.091 0.763 ↓ 1.0 795 1 / 3

Hash (cost=20.94..20.94 rows=794 width=14) (actual time=2.288..2.288 rows=795 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 46kB
15. 0.671 0.671 ↓ 1.0 795 1 / 3

Seq Scan on market m (cost=0.00..20.94 rows=794 width=14) (actual time=0.014..2.014 rows=795 loops=1)

16.          

SubPlan (for Gather)

17. 0.027 0.027 ↓ 0.0 0 1

Index Scan using instr_float_attr_pk on instr_float_attr (cost=0.15..8.17 rows=1 width=30) (actual time=0.027..0.027 rows=0 loops=1)

  • Index Cond: ((tid = i.tid) AND (attr_type_id = 42))