explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J6Rh

Settings
# exclusive inclusive rows x rows loops node
1. 11.250 52,810.346 ↓ 9.7 10,965 1

Result (cost=159,867.18..252,232.63 rows=1,130 width=652) (actual time=8,131.342..52,810.346 rows=10,965 loops=1)

  • One-Time Filter: ('All Trades'::text <> ALL ('{"All Open Trades","Open & Unconfirmed"}'::text[]))
2. 360.886 52,799.096 ↓ 9.7 10,965 1

Nested Loop (cost=159,867.18..252,232.63 rows=1,130 width=652) (actual time=8,131.339..52,799.096 rows=10,965 loops=1)

3. 213.369 51,835.104 ↓ 10,965.0 10,965 1

Gather (cost=159,863.23..236,008.53 rows=1 width=286) (actual time=8,131.072..51,835.104 rows=10,965 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 19.241 51,621.735 ↓ 3,655.0 3,655 3 / 3

Nested Loop (cost=158,863.23..235,008.43 rows=1 width=286) (actual time=8,506.380..51,621.735 rows=3,655 loops=3)

  • Join Filter: ((f.fund)::text = (t.subfund_abbrev)::text)
5. 118.711 51,565.869 ↓ 2,442.0 2,442 3 / 3

Nested Loop (cost=158,862.80..234,994.34 rows=1 width=240) (actual time=8,506.363..51,565.869 rows=2,442 loops=3)

6. 1,242.088 2,661.910 ↓ 51.2 43,249 3 / 3

Hash Join (cost=115,473.86..188,960.51 rows=844 width=148) (actual time=1,740.035..2,661.910 rows=43,249 loops=3)

  • Hash Cond: (f.order_id = o.order_id)
7. 882.178 882.178 ↓ 53.5 968,155 3 / 3

Parallel Seq Scan on flyallocs f (cost=0.00..70,876.37 rows=18,092 width=36) (actual time=0.008..882.178 rows=968,155 loops=3)

  • Filter: (('All Trades'::text = ANY ('{"All Trades","All Open Trades"}'::text[])) OR (COALESCE(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)))
8. 244.450 537.644 ↑ 1.0 129,748 3 / 3

Hash (cost=111,530.37..111,530.37 rows=135,479 width=112) (actual time=537.644..537.644 rows=129,748 loops=3)

  • Buckets: 32768 Batches: 8 Memory Usage: 2678kB
9. 274.910 293.194 ↑ 1.0 129,748 3 / 3

Bitmap Heap Scan on trade_order o (cost=3,477.11..111,530.37 rows=135,479 width=112) (actual time=19.106..293.194 rows=129,748 loops=3)

  • Recheck Cond: (("time" >= (to_date('20170101'::text, 'YYYYMMDD'::text) - ('6hour'::cstring)::interval)) AND ("time" <= (to_date('20170430'::text, 'YYYYMMDD'::text) + ('18hour'::cstring)::interval)))
  • Heap Blocks: exact=5523
10. 18.284 18.284 ↑ 1.0 129,748 3 / 3

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

  • Index Cond: (("time" >= (to_date('20170101'::text, 'YYYYMMDD'::text) - ('6hour'::cstring)::interval)) AND ("time" <= (to_date('20170430'::text, 'YYYYMMDD'::text) + ('18hour'::cstring)::interval)))
11. 287.379 48,785.248 ↓ 0.0 0 129,748 / 3

Hash Join (cost=43,388.94..43,392.07 rows=1 width=108) (actual time=1.126..1.128 rows=0 loops=129,748)

  • Hash Cond: (cit.instrument_type_id = i.instrument_type_id)
12. 58.616 58.616 ↑ 1.0 81 7,327 / 3

Seq Scan on instrument_type cit (cost=0.00..2.81 rows=81 width=19) (actual time=0.004..0.024 rows=81 loops=7,327)

13. 43.249 48,439.253 ↓ 0.0 0 129,748 / 3

Hash (cost=43,388.93..43,388.93 rows=1 width=93) (actual time=1.120..1.120 rows=0 loops=129,748)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
14. 86.499 48,396.004 ↓ 0.0 0 129,748 / 3

Nested Loop (cost=43,372.33..43,388.93 rows=1 width=93) (actual time=1.090..1.119 rows=0 loops=129,748)

15. 16,218.500 47,833.763 ↑ 1.0 1 129,748 / 3

Nested Loop (cost=43,367.82..43,377.42 rows=1 width=23) (actual time=0.583..1.106 rows=1 loops=129,748)

  • Join Filter: (tr.trader_id = t_1.trader_id)
  • Rows Removed by Join Filter: 1498
16. 216.247 216.247 ↑ 1.0 1 129,748 / 3

Index Scan using trader_pk on trader tr (cost=0.28..5.38 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=129,748)

  • Index Cond: (trader_id = o.trader_id)
17. 24,706.288 31,399.016 ↓ 7.5 1,499 129,748 / 3

HashAggregate (cost=43,367.54..43,369.54 rows=200 width=4) (actual time=0.155..0.726 rows=1,499 loops=129,748)

  • Group Key: t_1.trader_id
18. 14.481 6,692.728 ↑ 1.0 1,499 3 / 3

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

  • 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)
19. 6,337.476 6,678.247 ↑ 3.4 2,997 3 / 3

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

  • 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
20. 0.498 0.498 ↑ 1.0 1,499 3 / 3

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

21. 339.070 340.273 ↓ 2.2 1,499 4,497 / 3

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

22. 0.266 1.203 ↓ 2.2 1,499 3 / 3

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

23. 0.032 0.032 ↑ 1.0 1 3 / 3

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

  • Index Cond: (upper((email)::text) = 'QAUSER1'::text)
24. 0.905 0.905 ↓ 1.8 1,499 3 / 3

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

  • Index Cond: ((user_id = tu.old_tudor_id) AND (applic = 'Orders'::text) AND (client_id = tu.client_id))
  • Heap Fetches: 1499
25. 475.743 475.743 ↓ 0.0 0 129,748 / 3

Index Scan using instrument_pk on instrument i (cost=4.51..11.50 rows=1 width=70) (actual time=0.011..0.011 rows=0 loops=129,748)

  • Index Cond: (tid = o.ins_iid)
  • Filter: (((instrument_type_id)::text = '26'::text) AND ((tid > 500000000) OR (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
26.          

SubPlan (for Index Scan)

27. 0.000 0.000 ↓ 0.0 0 / 3

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

  • Filter: (companyid = 228)
28. 36.625 36.625 ↑ 7.0 1 7,325 / 3

Index Scan using trade_ind6 on trade t (cost=0.43..14.00 rows=7 width=64) (actual time=0.013..0.015 rows=1 loops=7,325)

  • Index Cond: (order_id = o.order_id)
29. 21.930 21.930 ↑ 2,260.0 1 10,965

Seq Scan on zz_100042200_tidlist ff (cost=0.00..32.60 rows=2,260 width=0) (actual time=0.001..0.002 rows=1 loops=10,965)

30.          

SubPlan (for Nested Loop)

31. 33.631 581.145 ↓ 0.0 0 10,965

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

32. 197.370 197.370 ↑ 2.0 1 10,965

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

  • Index Cond: ((ps_id = t.ps_id) AND (ps_liquid = 'L'::text))
  • Heap Fetches: 10942
33. 350.144 350.144 ↓ 0.0 0 10,942

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

  • Index Cond: (order_id = tt.order_id)
  • Filter: ((exec_broker)::text ~~ 'ZZ%'::text)
  • Rows Removed by Filter: 1
34. 0.031 0.031 ↑ 1.0 43 1

Seq Scan on recon_broker_codes (cost=0.00..3.84 rows=43 width=5) (actual time=0.007..0.031 rows=43 loops=1)

  • Filter: (companyid = 228)
  • Rows Removed by Filter: 104