explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A6r1

Settings
# exclusive inclusive rows x rows loops node
1. 13.168 45,138.186 ↑ 61.4 10,965 1

Result (cost=184,631.93..10,004,827.65 rows=673,480 width=652) (actual time=41,484.891..45,138.186 rows=10,965 loops=1)

  • Output: i.tid, tr.name, o.notes, f.custody_ref, o.order_id, o."time", o.orig_date, o.clear_agent, o.exec_broker, (CASE WHEN (i.instrument_type_id = ANY ('{4,71}'::integer[])) THEN (concat(i.name, ' ', to_char(o.settlement, 'Mon dd, yyyy'::text)))::character varying ELSE i.name END), cit.name, t.fund_abbrev, f.fund, o.strategy, t.ps_liquid, t.ps_id, t.ps_time, f.quantity, t.quantity, o.quantity, (CASE WHEN ((t.ps_id = 0) OR ((t.ps_id > 0) AND (to_timestamp(to_char(t.ps_time, 'YYYY-MON-DD HH24:MI:SS'::text), 'YYYY-MON-DD HH24:MI:SS'::text) > (to_date('20180101'::text, 'YYYYMMDD'::text) - '1 day'::interval)))) THEN t.quantity ELSE '0'::numeric END), (CASE WHEN ((t.ps_id > 0) AND (to_timestamp(to_char(t.ps_time, 'YYYY-MON-DD HH24:MI:SS'::text), 'YYYY-MON-DD HH24:MI:SS'::text) <= (to_date('20180101'::text, 'YYYYMMDD'::text) - '1 day'::interval))) THEN t.quantity ELSE '0'::numeric END), (CASE WHEN (SubPlan 1) THEN t.quantity ELSE '0'::numeric END), (((t.quantity * t.price) * i.price_factor)), i.birth_date, t.price, (CASE WHEN (COALESCE(f.cnfrm_dt, (to_date('1-1-1900'::text, 'DD-MM-YYYY'::text))::timestamp without time zone) <> to_date('1-1-1900'::text, 'DD-MM-YYYY'::text)) THEN 'CONFIRMED'::text WHEN (date_part('day'::text, ((to_date('20180101'::text, 'YYYYMMDD'::text) - ('1day'::cstring)::interval) - o.orig_date)) >= '90'::double precision) THEN 'Over 90'::text WHEN ((date_part('day'::text, ((to_date('20180101'::text, 'YYYYMMDD'::text) - ('1day'::cstring)::interval) - o.orig_date)) >= '60'::double precision) AND (date_part('day'::text, ((to_date('20180101'::text, 'YYYYMMDD'::text) - ('1day'::cstring)::interval) - o.orig_date)) < '90'::double precision)) THEN '60-90'::text WHEN ((date_part('day'::text, ((to_date('20180101'::text, 'YYYYMMDD'::text) - ('1day'::cstring)::interval) - o.orig_date)) >= '30'::double precision) AND (date_part('day'::text, ((to_date('20180101'::text, 'YYYYMMDD'::text) - ('1day'::cstring)::interval) - o.orig_date)) < '60'::double precision)) THEN '30-60'::text ELSE 'Under 30'::text END), (date_part('day'::text, ((to_date('20180101'::text, 'YYYYMMDD'::text) - ('1day'::cstring)::interval) - o.orig_date))), o.settlement, o.ins_type, i.price_factor, i.currency_code, o.repo_start_date, o.repo_end_date, o.repo_rate, t.fill_id, (CASE WHEN ((o.repo)::text = ANY ('{O,T}'::text[])) THEN 'Y'::text ELSE 'N'::text END), (CASE WHEN ((i.instrument_type_id = ANY ('{26,31}'::integer[])) AND (NOT (hashed SubPlan 2))) THEN 'IGNORABLE EQY SWAP'::text ELSE 'NO'::text END), i.instrument_type_id, o.trd_cur, (COALESCE(o.trd_rate, 1.0000)), (COALESCE(t.commission, '0'::numeric)), (COALESCE(t.tax_rate, '0'::numeric)), ((COALESCE(t.misc_expenses, '0'::numeric) * '-1'::numeric)), ((COALESCE(t.misc_fees, '0'::numeric) * '-1'::numeric)), i.underlying_tid, f.cnfrm_stat, f.cnfrm_dt, o.posting_id, i.instrument_subtype_id, o.com_implied
  • One-Time Filter: ('All Trades'::text <> ALL ('{"All Open Trades","Open & Unconfirmed"}'::text[]))
  • Buffers: shared hit=3258230 read=71606, local hit=1, temp read=102446 written=102756
2. 209.039 45,125.018 ↑ 61.4 10,965 1

Nested Loop (cost=184,631.93..10,004,827.65 rows=673,480 width=652) (actual time=41,484.888..45,125.018 rows=10,965 loops=1)

  • Output: i.tid, tr.name, o.notes, f.custody_ref, o.order_id, o."time", o.orig_date, o.clear_agent, o.exec_broker, CASE WHEN (i.instrument_type_id = ANY ('{4,71}'::integer[])) THEN (concat(i.name, ' ', to_char(o.settlement, 'Mon dd, yyyy'::text)))::character varying ELSE i.name END, cit.name, t.fund_abbrev, f.fund, o.strategy, t.ps_liquid, t.ps_id, t.ps_time, f.quantity, t.quantity, o.quantity, CASE WHEN ((t.ps_id = 0) OR ((t.ps_id > 0) AND (to_timestamp(to_char(t.ps_time, 'YYYY-MON-DD HH24:MI:SS'::text), 'YYYY-MON-DD HH24:MI:SS'::text) > (to_date('20180101'::text, 'YYYYMMDD'::text) - '1 day'::interval)))) THEN t.quantity ELSE '0'::numeric END, CASE WHEN ((t.ps_id > 0) AND (to_timestamp(to_char(t.ps_time, 'YYYY-MON-DD HH24:MI:SS'::text), 'YYYY-MON-DD HH24:MI:SS'::text) <= (to_date('20180101'::text, 'YYYYMMDD'::text) - '1 day'::interval))) THEN t.quantity ELSE '0'::numeric END, CASE WHEN (SubPlan 1) THEN t.quantity ELSE '0'::numeric END, ((t.quantity * t.price) * i.price_factor), i.birth_date, t.price, CASE WHEN (COALESCE(f.cnfrm_dt, (to_date('1-1-1900'::text, 'DD-MM-YYYY'::text))::timestamp without time zone) <> to_date('1-1-1900'::text, 'DD-MM-YYYY'::text)) THEN 'CONFIRMED'::text WHEN (date_part('day'::text, ((to_date('20180101'::text, 'YYYYMMDD'::text) - ('1day'::cstring)::interval) - o.orig_date)) >= '90'::double precision) THEN 'Over 90'::text WHEN ((date_part('day'::text, ((to_date('20180101'::text, 'YYYYMMDD'::text) - ('1day'::cstring)::interval) - o.orig_date)) >= '60'::double precision) AND (date_part('day'::text, ((to_date('20180101'::text, 'YYYYMMDD'::text) - ('1day'::cstring)::interval) - o.orig_date)) < '90'::double precision)) THEN '60-90'::text WHEN ((date_part('day'::text, ((to_date('20180101'::text, 'YYYYMMDD'::text) - ('1day'::cstring)::interval) - o.orig_date)) >= '30'::double precision) AND (date_part('day'::text, ((to_date('20180101'::text, 'YYYYMMDD'::text) - ('1day'::cstring)::interval) - o.orig_date)) < '60'::double precision)) THEN '30-60'::text ELSE 'Under 30'::text END, date_part('day'::text, ((to_date('20180101'::text, 'YYYYMMDD'::text) - ('1day'::cstring)::interval) - o.orig_date)), o.settlement, o.ins_type, i.price_factor, i.currency_code, o.repo_start_date, o.repo_end_date, o.repo_rate, t.fill_id, CASE WHEN ((o.repo)::text = ANY ('{O,T}'::text[])) THEN 'Y'::text ELSE 'N'::text END, CASE WHEN ((i.instrument_type_id = ANY ('{26,31}'::integer[])) AND (NOT (hashed SubPlan 2))) THEN 'IGNORABLE EQY SWAP'::text ELSE 'NO'::text END, i.instrument_type_id, o.trd_cur, COALESCE(o.trd_rate, 1.0000), COALESCE(t.commission, '0'::numeric), COALESCE(t.tax_rate, '0'::numeric), (COALESCE(t.misc_expenses, '0'::numeric) * '-1'::numeric), (COALESCE(t.misc_fees, '0'::numeric) * '-1'::numeric), i.underlying_tid, f.cnfrm_stat, f.cnfrm_dt, o.posting_id, i.instrument_subtype_id, o.com_implied
  • Buffers: shared hit=3258230 read=71606, local hit=1, temp read=102446 written=102756
3. 0.007 0.007 ↑ 2,260.0 1 1

Seq Scan on pg_temp_16.zz_100042200_tidlist ff (cost=0.00..32.60 rows=2,260 width=0) (actual time=0.006..0.007 rows=1 loops=1)

  • Output: ff.tid
  • Buffers: local hit=1
4. 34.637 44,696.636 ↓ 36.8 10,965 1

Materialize (cost=184,627.98..362,061.39 rows=298 width=286) (actual time=41,484.746..44,696.636 rows=10,965 loops=1)

  • Output: i.tid, i.instrument_type_id, i.name, i.price_factor, i.birth_date, i.currency_code, i.underlying_tid, i.instrument_subtype_id, cit.name, tr.name, t.fund_abbrev, t.ps_liquid, t.ps_id, t.ps_time, t.quantity, t.price, t.fill_id, t.commission, t.tax_rate, t.misc_expenses, t.misc_fees, o.notes, o.order_id, o."time", o.orig_date, o.clear_agent, o.exec_broker, o.settlement, o.strategy, o.quantity, o.ins_type, o.repo_start_date, o.repo_end_date, o.repo_rate, o.repo, o.trd_cur, o.trd_rate, o.posting_id, o.com_implied, f.custody_ref, f.fund, f.quantity, f.cnfrm_dt, f.cnfrm_stat
  • Buffers: shared hit=3170445 read=71606, temp read=102446 written=102756
5. 0.000 44,661.999 ↓ 36.8 10,965 1

Gather (cost=184,627.98..362,059.90 rows=298 width=286) (actual time=41,484.739..44,661.999 rows=10,965 loops=1)

  • Output: i.tid, i.instrument_type_id, i.name, i.price_factor, i.birth_date, i.currency_code, i.underlying_tid, i.instrument_subtype_id, cit.name, tr.name, t.fund_abbrev, t.ps_liquid, t.ps_id, t.ps_time, t.quantity, t.price, t.fill_id, t.commission, t.tax_rate, t.misc_expenses, t.misc_fees, o.notes, o.order_id, o."time", o.orig_date, o.clear_agent, o.exec_broker, o.settlement, o.strategy, o.quantity, o.ins_type, o.repo_start_date, o.repo_end_date, o.repo_rate, o.repo, o.trd_cur, o.trd_rate, o.posting_id, o.com_implied, f.custody_ref, f.fund, f.quantity, f.cnfrm_dt, f.cnfrm_stat
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=3170445 read=71606, temp read=102446 written=102362
6. 3.146 44,767.958 ↓ 29.5 3,655 3 / 3

Hash Join (cost=183,627.98..361,030.10 rows=124 width=286) (actual time=41,956.650..44,767.958 rows=3,655 loops=3)

  • Output: i.tid, i.instrument_type_id, i.name, i.price_factor, i.birth_date, i.currency_code, i.underlying_tid, i.instrument_subtype_id, cit.name, tr.name, t.fund_abbrev, t.ps_liquid, t.ps_id, t.ps_time, t.quantity, t.price, t.fill_id, t.commission, t.tax_rate, t.misc_expenses, t.misc_fees, o.notes, o.order_id, o."time", o.orig_date, o.clear_agent, o.exec_broker, o.settlement, o.strategy, o.quantity, o.ins_type, o.repo_start_date, o.repo_end_date, o.repo_rate, o.repo, o.trd_cur, o.trd_rate, o.posting_id, o.com_implied, f.custody_ref, f.fund, f.quantity, f.cnfrm_dt, f.cnfrm_stat
  • Hash Cond: (o.trader_id = tr.trader_id)
  • Buffers: shared hit=3170445 read=71606, temp read=102446 written=102362
  • Worker 0: actual time=42435.232..44802.630 rows=3927 loops=1
  • Buffers: shared hit=1089750 read=19875, temp read=32507 written=32479
  • Worker 1: actual time=41952.814..44857.080 rows=3926 loops=1
  • Buffers: shared hit=1068290 read=24023, temp read=34197 written=34169
7. 31.827 44,764.036 ↓ 29.5 3,655 3 / 3

Nested Loop (cost=183,574.25..360,974.67 rows=124 width=279) (actual time=41,955.853..44,764.036 rows=3,655 loops=3)

  • Output: i.tid, i.instrument_type_id, i.name, i.price_factor, i.birth_date, i.currency_code, i.underlying_tid, i.instrument_subtype_id, cit.name, t.fund_abbrev, t.ps_liquid, t.ps_id, t.ps_time, t.quantity, t.price, t.fill_id, t.commission, t.tax_rate, t.misc_expenses, t.misc_fees, o.notes, o.order_id, o."time", o.orig_date, o.clear_agent, o.exec_broker, o.settlement, o.strategy, o.quantity, o.ins_type, o.repo_start_date, o.repo_end_date, o.repo_rate, o.repo, o.trd_cur, o.trd_rate, o.posting_id, o.com_implied, o.trader_id, f.custody_ref, f.fund, f.quantity, f.cnfrm_dt, f.cnfrm_stat, t_1.trader_id
  • Buffers: shared hit=3170385 read=71606, temp read=102446 written=102362
  • Worker 0: actual time=42434.456..44798.545 rows=3927 loops=1
  • Buffers: shared hit=1089730 read=19875, temp read=32507 written=32479
  • Worker 1: actual time=41952.014..44853.009 rows=3926 loops=1
  • Buffers: shared hit=1068270 read=24023, temp read=34197 written=34169
8. 2,573.713 44,221.854 ↓ 35.1 63,794 3 / 3

Hash Join (cost=183,569.74..340,070.12 rows=1,817 width=217) (actual time=40,337.681..44,221.854 rows=63,794 loops=3)

  • Output: cit.name, cit.instrument_type_id, t.fund_abbrev, t.ps_liquid, t.ps_id, t.ps_time, t.quantity, t.price, t.fill_id, t.commission, t.tax_rate, t.misc_expenses, t.misc_fees, o.notes, o.order_id, o."time", o.orig_date, o.clear_agent, o.exec_broker, o.settlement, o.strategy, o.quantity, o.ins_type, o.repo_start_date, o.repo_end_date, o.repo_rate, o.repo, o.trd_cur, o.trd_rate, o.posting_id, o.com_implied, o.ins_iid, o.trader_id, f.custody_ref, f.fund, f.quantity, f.cnfrm_dt, f.cnfrm_stat, t_1.trader_id
  • Hash Cond: ((t.order_id = o.order_id) AND ((t.subfund_abbrev)::text = (f.fund)::text))
  • Buffers: shared hit=2213089 read=71606, temp read=102446 written=102362
  • Worker 0: actual time=40466.069..44213.464 rows=70359 loops=1
  • Buffers: shared hit=737788 read=19875, temp read=32507 written=32479
  • Worker 1: actual time=40876.608..44264.858 rows=66097 loops=1
  • Buffers: shared hit=737659 read=24023, temp read=34197 written=34169
9. 1,600.606 1,600.606 ↑ 1.2 1,289,135 3 / 3

Parallel Seq Scan on public.trade t (cost=0.00..108,122.84 rows=1,611,384 width=64) (actual time=0.012..1,600.606 rows=1,289,135 loops=3)

  • Output: t.fund_abbrev, t.ps_liquid, t.ps_id, t.ps_time, t.quantity, t.price, t.fill_id, t.commission, t.tax_rate, t.misc_expenses, t.misc_fees, t.order_id, t.subfund_abbrev
  • Buffers: shared hit=20403 read=71606
  • Worker 0: actual time=0.005..1469.206 rows=1125065 loops=1
  • Buffers: shared hit=6883 read=19875
  • Worker 1: actual time=0.017..1585.363 rows=1293410 loops=1
  • Buffers: shared hit=6754 read=24023
10. 291.932 40,047.535 ↓ 1.9 129,748 3 / 3

Hash (cost=180,899.64..180,899.64 rows=67,740 width=171) (actual time=40,047.534..40,047.535 rows=129,748 loops=3)

  • Output: cit.name, cit.instrument_type_id, o.notes, o.order_id, o."time", o.orig_date, o.clear_agent, o.exec_broker, o.settlement, o.strategy, o.quantity, o.ins_type, o.repo_start_date, o.repo_end_date, o.repo_rate, o.repo, o.trd_cur, o.trd_rate, o.posting_id, o.com_implied, o.ins_iid, o.trader_id, f.custody_ref, f.fund, f.quantity, f.cnfrm_dt, f.cnfrm_stat, f.order_id, t_1.trader_id
  • Buckets: 32768 (originally 32768) Batches: 8 (originally 4) Memory Usage: 3841kB
  • Buffers: shared hit=2192686, temp read=53949 written=61893
  • Worker 0: actual time=40417.709..40417.709 rows=129748 loops=1
  • Buffers: shared hit=730905, temp read=17983 written=20631
  • Worker 1: actual time=40058.943..40058.943 rows=129748 loops=1
  • Buffers: shared hit=730905, temp read=17983 written=20631
11. 3,580.691 39,755.603 ↓ 1.9 129,748 3 / 3

Hash Join (cost=49,930.84..180,899.64 rows=67,740 width=171) (actual time=37,298.999..39,755.603 rows=129,748 loops=3)

  • Output: cit.name, cit.instrument_type_id, o.notes, o.order_id, o."time", o.orig_date, o.clear_agent, o.exec_broker, o.settlement, o.strategy, o.quantity, o.ins_type, o.repo_start_date, o.repo_end_date, o.repo_rate, o.repo, o.trd_cur, o.trd_rate, o.posting_id, o.com_implied, o.ins_iid, o.trader_id, f.custody_ref, f.fund, f.quantity, f.cnfrm_dt, f.cnfrm_stat, f.order_id, t_1.trader_id
  • Hash Cond: (f.order_id = o.order_id)
  • Buffers: shared hit=2192686, temp read=53949 written=53907
  • Worker 0: actual time=37734.201..40127.010 rows=129748 loops=1
  • Buffers: shared hit=730905, temp read=17983 written=17969
  • Worker 1: actual time=37353.453..39743.385 rows=129748 loops=1
  • Buffers: shared hit=730905, temp read=17983 written=17969
12. 2,185.464 2,185.464 ↑ 1.0 2,904,466 3 / 3

Seq Scan on public.flyallocs f (cost=0.00..72,691.66 rows=2,904,466 width=36) (actual time=0.010..2,185.464 rows=2,904,466 loops=3)

  • Output: f.custody_ref, f.fund, f.quantity, f.cnfrm_dt, f.cnfrm_stat, f.order_id
  • Buffers: shared hit=130941
  • Worker 0: actual time=0.012..2221.965 rows=2904466 loops=1
  • Buffers: shared hit=43647
  • Worker 1: actual time=0.010..2183.320 rows=2904466 loops=1
  • Buffers: shared hit=43647
13. 255.268 33,989.448 ↓ 1.9 129,748 3 / 3

Hash (cost=47,760.09..47,760.09 rows=67,740 width=135) (actual time=33,989.448..33,989.448 rows=129,748 loops=3)

  • Output: cit.name, cit.instrument_type_id, o.notes, o.order_id, o."time", o.orig_date, o.clear_agent, o.exec_broker, o.settlement, o.strategy, o.quantity, o.ins_type, o.repo_start_date, o.repo_end_date, o.repo_rate, o.repo, o.trd_cur, o.trd_rate, o.posting_id, o.com_implied, o.ins_iid, o.trader_id, t_1.trader_id
  • Buckets: 32768 (originally 32768) Batches: 8 (originally 4) Memory Usage: 3841kB
  • Buffers: shared hit=2061695, temp written=6615
  • Worker 0: actual time=34304.866..34304.866 rows=129748 loops=1
  • Buffers: shared hit=687233, temp written=2205
  • Worker 1: actual time=33957.665..33957.665 rows=129748 loops=1
  • Buffers: shared hit=687233, temp written=2205
14. 226.362 33,734.180 ↓ 1.9 129,748 3 / 3

Nested Loop (cost=46,929.82..47,760.09 rows=67,740 width=135) (actual time=6,537.379..33,734.180 rows=129,748 loops=3)

  • Output: cit.name, cit.instrument_type_id, o.notes, o.order_id, o."time", o.orig_date, o.clear_agent, o.exec_broker, o.settlement, o.strategy, o.quantity, o.ins_type, o.repo_start_date, o.repo_end_date, o.repo_rate, o.repo, o.trd_cur, o.trd_rate, o.posting_id, o.com_implied, o.ins_iid, o.trader_id, t_1.trader_id
  • Buffers: shared hit=2061695
  • Worker 0: actual time=6629.544..34068.904 rows=129748 loops=1
  • Buffers: shared hit=687233
  • Worker 1: actual time=6604.311..33697.159 rows=129748 loops=1
  • Buffers: shared hit=687233
15. 1.456 6,483.846 ↓ 1,499.0 1,499 3 / 3

Nested Loop (cost=43,367.54..43,374.55 rows=1 width=23) (actual time=6,474.423..6,483.846 rows=1,499 loops=3)

  • Output: cit.name, cit.instrument_type_id, t_1.trader_id
  • Buffers: shared hit=175
  • Worker 0: actual time=6568.069..6576.233 rows=1499 loops=1
  • Buffers: shared hit=59
  • Worker 1: actual time=6539.956..6552.141 rows=1499 loops=1
  • Buffers: shared hit=59
16. 0.023 0.023 ↑ 1.0 1 3 / 3

Seq Scan on public.instrument_type cit (cost=0.00..3.01 rows=1 width=19) (actual time=0.010..0.023 rows=1 loops=3)

  • Output: cit.client_id, cit.instrument_type_id, cit.instrument_class_id, cit.name, cit.price_table_name, cit.display_group_id, cit.old_price_table, cit.old_tudor_code, cit.price_factor, cit.trade_entry_code, cit.confirm_flag, cit.derivative_flag, cit.trader_creates_flag, cit.derive_settle_flag, cit.option_instrument_type_id, cit.acc_mgr_flag, cit.default_instr_status_id, cit.default_ps_rule_id, cit.liqd_analysis_flag, cit.rtprice_rule_id, cit.rt_message_id, cit.price_by_ulyer_flag, cit.rt_name_rule_id, cit.order_level_comm_flag, cit.order_level_price_flag, cit.exchange_tradable_flag, cit.jpricer_group_id, cit.ps_settle_max_flag, cit.settlement_status_enabled
  • Filter: (cit.instrument_type_id = 26)
  • Rows Removed by Filter: 80
  • Buffers: shared hit=6
  • Worker 0: actual time=0.011..0.024 rows=1 loops=1
  • Buffers: shared hit=2
  • Worker 1: actual time=0.012..0.024 rows=1 loops=1
  • Buffers: shared hit=2
17. 8.533 6,482.367 ↓ 7.5 1,499 3 / 3

HashAggregate (cost=43,367.54..43,369.54 rows=200 width=4) (actual time=6,474.411..6,482.367 rows=1,499 loops=3)

  • Output: t_1.trader_id
  • Group Key: t_1.trader_id
  • Buffers: shared hit=169
  • Worker 0: actual time=6568.056..6574.710 rows=1499 loops=1
  • Buffers: shared hit=57
  • Worker 1: actual time=6539.942..6550.630 rows=1499 loops=1
  • Buffers: shared hit=57
18. 7.797 6,473.834 ↑ 1.0 1,499 3 / 3

HashAggregate (cost=43,318.82..43,348.80 rows=1,499 width=110) (actual time=6,473.004..6,473.834 rows=1,499 loops=3)

  • Output: t_1.client_id, t_1.trader_id, t_1.name, t_1.allocation_id, t_1.start_date, t_1.end_date, t_1.privdb, t_1.abbrev, t_1.synthetic_flag, t_1.trader_group_id, t_1.option_price_flag, t_1.admin_flag, tu.email, ((((t_1.client_id)::numeric * '100000'::numeric) + (t_1.trader_id)::numeric))
  • Group Key: t_1.client_id, t_1.trader_id, t_1.name, t_1.allocation_id, t_1.start_date, t_1.end_date, t_1.privdb, t_1.abbrev, t_1.synthetic_flag, t_1.trader_group_id, t_1.option_price_flag, t_1.admin_flag, tu.email, (((t_1.client_id)::numeric * '100000'::numeric) + (t_1.trader_id)::numeric)
  • Buffers: shared hit=169
  • Worker 0: actual time=6566.638..6567.455 rows=1499 loops=1
  • Buffers: shared hit=57
  • Worker 1: actual time=6538.495..6539.333 rows=1499 loops=1
  • Buffers: shared hit=57
19. 6,122.315 6,466.037 ↑ 3.4 2,997 3 / 3

Nested Loop (cost=0.84..42,961.30 rows=10,215 width=110) (actual time=0.089..6,466.037 rows=2,997 loops=3)

  • Output: t_1.client_id, t_1.trader_id, t_1.name, t_1.allocation_id, t_1.start_date, t_1.end_date, t_1.privdb, t_1.abbrev, t_1.synthetic_flag, t_1.trader_group_id, t_1.option_price_flag, t_1.admin_flag, tu.email, (((t_1.client_id)::numeric * '100000'::numeric) + (t_1.trader_id)::numeric)
  • Join Filter: (((p.trader_id)::numeric = '0'::numeric) OR ((((p.client_id)::numeric * '100000'::numeric) + (p.trader_id)::numeric) = (((t_1.client_id)::numeric * '100000'::numeric) + (t_1.trader_id)::numeric)))
  • Rows Removed by Join Filter: 2244004
  • Buffers: shared hit=169
  • Worker 0: actual time=0.095..6560.851 rows=2997 loops=1
  • Buffers: shared hit=57
  • Worker 1: actual time=0.100..6528.686 rows=2997 loops=1
  • Buffers: shared hit=57
20. 0.451 0.451 ↑ 1.0 1,499 3 / 3

Seq Scan on public.trader t_1 (cost=0.00..34.99 rows=1,499 width=70) (actual time=0.006..0.451 rows=1,499 loops=3)

  • Output: t_1.client_id, t_1.trader_id, t_1.name, t_1.allocation_id, t_1.start_date, t_1.end_date, t_1.privdb, t_1.abbrev, t_1.synthetic_flag, t_1.trader_group_id, t_1.option_price_flag, t_1.admin_flag
  • Buffers: shared hit=60
  • Worker 0: actual time=0.005..0.462 rows=1499 loops=1
  • Buffers: shared hit=20
  • Worker 1: actual time=0.009..0.449 rows=1499 loops=1
  • Buffers: shared hit=20
21. 339.384 343.271 ↓ 2.2 1,499 4,497 / 3

Materialize (cost=0.84..1,873.18 rows=683 width=16) (actual time=0.000..0.229 rows=1,499 loops=4,497)

  • Output: p.client_id, p.trader_id, tu.email
  • Buffers: shared hit=109
  • Worker 0: actual time=0.000..0.263 rows=1499 loops=1499
  • Buffers: shared hit=37
  • Worker 1: actual time=0.000..0.187 rows=1499 loops=1499
  • Buffers: shared hit=37
22. 0.263 3.887 ↓ 2.2 1,499 3 / 3

Nested Loop (cost=0.84..1,869.77 rows=683 width=16) (actual time=0.074..3.887 rows=1,499 loops=3)

  • Output: p.client_id, p.trader_id, tu.email
  • Buffers: shared hit=109
  • Worker 0: actual time=0.078..9.255 rows=1499 loops=1
  • Buffers: shared hit=37
  • Worker 1: actual time=0.083..1.246 rows=1499 loops=1
  • Buffers: shared hit=37
23. 0.022 0.022 ↑ 1.0 1 3 / 3

Index Scan using tudor_user_ind6 on public.tudor_user tu (cost=0.28..8.30 rows=1 width=16) (actual time=0.021..0.022 rows=1 loops=3)

  • Output: tu.client_id, tu.tudor_user_id, tu.department_id, tu.location_id, tu.first_name, tu.last_name, tu.work_phone_no, tu.home_phone_no, tu.email, tu.start_date, tu.end_date, tu.old_tudor_id, tu.home_address1, tu.home_address2, tu.home_address3, tu.private_flag, tu.trader_flag, tu.unix_id, tu.coda_code, tu.employee_status_id, tu.job_title_id, tu.social_security_no, tu.sex, tu.new_york_only_flag, tu.subtrader_flag, tu.executing_flag, tu.is_person_flag, tu.city, tu.state, tu.zip, tu.country_id
  • Index Cond: (upper((tu.email)::text) = 'QAUSER1'::text)
  • Buffers: shared hit=11
  • Worker 0: actual time=0.025..0.026 rows=1 loops=1
  • Buffers: shared hit=4
  • Worker 1: actual time=0.024..0.025 rows=1 loops=1
  • Buffers: shared hit=4
24. 3.602 3.602 ↓ 1.8 1,499 3 / 3

Index Only Scan using permissions_ind1 on public.permissions p (cost=0.56..1,853.23 rows=824 width=12) (actual time=0.050..3.602 rows=1,499 loops=3)

  • Output: p.user_id, p.applic, p.trader_id, p.ins_type, p.ins_iid, p.ins_grp, p.country, p.client_id
  • Index Cond: ((p.user_id = tu.old_tudor_id) AND (p.applic = 'Orders'::text) AND (p.client_id = tu.client_id))
  • Heap Fetches: 1499
  • Buffers: shared hit=98
  • Worker 0: actual time=0.051..8.965 rows=1499 loops=1
  • Buffers: shared hit=33
  • Worker 1: actual time=0.057..0.954 rows=1499 loops=1
  • Buffers: shared hit=33
25. 187.375 27,023.972 ↑ 3.0 87 4,497 / 3

Bitmap Heap Scan on public.trade_order o (cost=3,562.28..4,382.92 rows=261 width=112) (actual time=17.941..18.028 rows=87 loops=4,497)

  • Output: o.client_id, o.order_id, o.quantity, o.user_id, o."time", o.created, o.status, o.trader_id, o.ins_type, o.ins_iid, o.settlement, o.alloc_fund, o.is_alloc, o.clear_agent, o.exec_broker, o.deliv_instruct, o.initiate, o.notes, o.alloc_rule, o.startflys, o.com_type, o.commission, o.com_turn, o.com_implied, o.trd_cur, o.trd_rate, o.trd_freeze, o.strategy, o.parent_id, o.link_id, o.repo, o.repo_rate, o.repo_calendar, o.repo_start_date, o.repo_end_date, o.kind_flag, o.last_repo, o.ex_cpn, o.gross, o.xiid, o.quantity_curr, o.master_order_id, o.unfilled_qty, o.avg_fill_price, o.ps_rule_id, o.orig_date, o.posting_id, o.executing_user_id, o.repo_close_date, o.repo_price, o.direct_fx_flag, o.tax, o.fix_status_id, o.txn_type_id, o.gen_date, o.yield, o.value_date, o.post_fees_on_initiate, o.com_source, o.repo_original_start_date, o.tax_rate, o.misc_expenses, o.misc_fees, o.boxed_id, o.is_boxed, o.counterparty, o.payment_date, o.actual_maturity_date, o.deal_maturity_date, o.deal_ord_type, o.init_orig_date, o.init_trade_time, o.link_factor, o.deal_group_id, o.txn_version
  • Recheck Cond: ((o.trader_id = t_1.trader_id) AND (o."time" >= (to_date('20170101'::text, 'YYYYMMDD'::text) - ('6hour'::cstring)::interval)) AND (o."time" <= (to_date('20170430'::text, 'YYYYMMDD'::text) + ('18hour'::cstring)::interval)))
  • Heap Blocks: exact=33054
  • Buffers: shared hit=2061520
  • Worker 0: actual time=18.083..18.174 rows=87 loops=1499
  • Buffers: shared hit=687174
  • Worker 1: actual time=17.873..17.959 rows=87 loops=1499
  • Buffers: shared hit=687174
26. 114.868 26,836.597 ↓ 0.0 0 4,497 / 3

BitmapAnd (cost=3,562.28..3,562.28 rows=261 width=0) (actual time=17.903..17.903 rows=0 loops=4,497)

  • Buffers: shared hit=1962358
  • Worker 0: actual time=18.047..18.047 rows=0 loops=1499
  • Buffers: shared hit=654120
  • Worker 1: actual time=17.834..17.834 rows=0 loops=1499
  • Buffers: shared hit=654120
27. 541.139 541.139 ↑ 2.9 1,738 4,497 / 3

Bitmap Index Scan on trade_order_ind8 (cost=0.00..84.85 rows=5,019 width=0) (actual time=0.361..0.361 rows=1,738 loops=4,497)

  • Index Cond: (o.trader_id = t_1.trader_id)
  • Buffers: shared hit=34856
  • Worker 0: actual time=0.371..0.371 rows=1738 loops=1499
  • Buffers: shared hit=11619
  • Worker 1: actual time=0.358..0.358 rows=1738 loops=1499
  • Buffers: shared hit=11619
28. 26,180.590 26,180.590 ↑ 1.0 129,748 3,855 / 3

Bitmap Index Scan on trade_order_ind7 (cost=0.00..3,443.24 rows=135,479 width=0) (actual time=20.374..20.374 rows=129,748 loops=3,855)

  • Index Cond: ((o."time" >= (to_date('20170101'::text, 'YYYYMMDD'::text) - ('6hour'::cstring)::interval)) AND (o."time" <= (to_date('20170430'::text, 'YYYYMMDD'::text) + ('18hour'::cstring)::interval)))
  • Buffers: shared hit=1927502
  • Worker 0: actual time=20.544..20.544 rows=129748 loops=1285
  • Buffers: shared hit=642501
  • Worker 1: actual time=20.288..20.288 rows=129748 loops=1285
  • Buffers: shared hit=642501
29. 510.355 510.355 ↓ 0.0 0 191,383 / 3

Index Scan using instrument_pk on public.instrument i (cost=4.51..11.49 rows=1 width=70) (actual time=0.007..0.008 rows=0 loops=191,383)

  • Output: i.client_id, i.tid, i.name, i.issuer_id, i.currency_code, i.country_id, i.instrument_type_id, i.tick_size, i.underlying_tid, i.birth_date, i.death_date, i.update_user_id, i.update_date, i.market_data_provider_id, i.local_code, i.primary_market_id, i.instr_status_id, i.price_base, i.rtname, i.tradable_flag, i.price_factor, i.equiv_future_id, i.display_base, i.master_tid, i.is_cashflow_override_allowed, i.calc_underlying_accruals, i.instrument_subtype_id, i.collateralized, i.incorp_country_id
  • Index Cond: (i.tid = o.ins_iid)
  • Filter: ((i.instrument_type_id = 26) AND ((i.tid > 500000000) OR (i.instrument_type_id = ANY ('{6,9,15,16,18,20,26,31,32,34,35,40,42,51,55,60,67,69,70,73,74,80,81}'::integer[])) OR (hashed SubPlan 3)))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=957296
  • Worker 0: actual time=0.007..0.007 rows=0 loops=70359
  • Buffers: shared hit=351942
  • Worker 1: actual time=0.008..0.008 rows=0 loops=66097
  • Buffers: shared hit=330611
30.          

SubPlan (for Index Scan)

31. 0.000 0.000 ↓ 0.0 0 / 3

Seq Scan on public.recon_broker_codes recon_broker_codes_1 (cost=0.00..3.84 rows=43 width=5) (never executed)

  • Output: recon_broker_codes_1.brokercode
  • Filter: (recon_broker_codes_1.companyid = 228)
32. 0.416 0.776 ↑ 1.0 1,499 3 / 3

Hash (cost=34.99..34.99 rows=1,499 width=19) (actual time=0.776..0.776 rows=1,499 loops=3)

  • Output: tr.name, tr.trader_id
  • Buckets: 2048 Batches: 1 Memory Usage: 95kB
  • Buffers: shared hit=60
  • Worker 0: actual time=0.753..0.754 rows=1499 loops=1
  • Buffers: shared hit=20
  • Worker 1: actual time=0.773..0.773 rows=1499 loops=1
  • Buffers: shared hit=20
33. 0.360 0.360 ↑ 1.0 1,499 3 / 3

Seq Scan on public.trader tr (cost=0.00..34.99 rows=1,499 width=19) (actual time=0.010..0.360 rows=1,499 loops=3)

  • Output: tr.name, tr.trader_id
  • Buffers: shared hit=60
  • Worker 0: actual time=0.010..0.338 rows=1499 loops=1
  • Buffers: shared hit=20
  • Worker 1: actual time=0.012..0.355 rows=1499 loops=1
  • Buffers: shared hit=20
34.          

SubPlan (for Nested Loop)

35. 22.114 219.300 ↓ 0.0 0 10,965

Nested Loop (cost=0.86..27.40 rows=1 width=4) (actual time=0.020..0.020 rows=0 loops=10,965)

  • Output: tt.ps_id
  • Buffers: shared hit=87783
36. 109.650 109.650 ↑ 2.0 1 10,965

Index Only Scan using trade_ind15 on public.trade tt (cost=0.43..10.48 rows=2 width=8) (actual time=0.009..0.010 rows=1 loops=10,965)

  • Output: tt.ps_id, tt.ps_liquid, tt.order_id
  • Index Cond: ((tt.ps_id = t.ps_id) AND (tt.ps_liquid = 'L'::text))
  • Heap Fetches: 10942
  • Buffers: shared hit=43920
37. 87.536 87.536 ↓ 0.0 0 10,942

Index Scan using trade_order_ind18 on public.trade_order oo (cost=0.43..8.45 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=10,942)

  • Output: oo.client_id, oo.order_id, oo.quantity, oo.user_id, oo."time", oo.created, oo.status, oo.trader_id, oo.ins_type, oo.ins_iid, oo.settlement, oo.alloc_fund, oo.is_alloc, oo.clear_agent, oo.exec_broker, oo.deliv_instruct, oo.initiate, oo.notes, oo.alloc_rule, oo.startflys, oo.com_type, oo.commission, oo.com_turn, oo.com_implied, oo.trd_cur, oo.trd_rate, oo.trd_freeze, oo.strategy, oo.parent_id, oo.link_id, oo.repo, oo.repo_rate, oo.repo_calendar, oo.repo_start_date, oo.repo_end_date, oo.kind_flag, oo.last_repo, oo.ex_cpn, oo.gross, oo.xiid, oo.quantity_curr, oo.master_order_id, oo.unfilled_qty, oo.avg_fill_price, oo.ps_rule_id, oo.orig_date, oo.posting_id, oo.executing_user_id, oo.repo_close_date, oo.repo_price, oo.direct_fx_flag, oo.tax, oo.fix_status_id, oo.txn_type_id, oo.gen_date, oo.yield, oo.value_date, oo.post_fees_on_initiate, oo.com_source, oo.repo_original_start_date, oo.tax_rate, oo.misc_expenses, oo.misc_fees, oo.boxed_id, oo.is_boxed, oo.counterparty, oo.payment_date, oo.actual_maturity_date, oo.deal_maturity_date, oo.deal_ord_type, oo.init_orig_date, oo.init_trade_time, oo.link_factor, oo.deal_group_id, oo.txn_version
  • Index Cond: (oo.order_id = tt.order_id)
  • Filter: ((oo.exec_broker)::text ~~ 'ZZ%'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=43863
38. 0.036 0.036 ↑ 1.0 43 1

Seq Scan on public.recon_broker_codes (cost=0.00..3.84 rows=43 width=5) (actual time=0.009..0.036 rows=43 loops=1)

  • Output: recon_broker_codes.brokercode
  • Filter: (recon_broker_codes.companyid = 228)
  • Rows Removed by Filter: 104
  • Buffers: shared hit=2