explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VviY

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 100,296.837 ↓ 0.0 0 1

Result (cost=122,832.76..1,138,671.00 rows=63,280 width=652) (actual time=100,296.837..100,296.837 rows=0 loops=1)

  • One-Time Filter: (' '::text <> ALL ('{"All Open Trades","Open & Unconfirmed"}'::text[]))
2. 90.289 100,296.835 ↓ 0.0 0 1

Nested Loop (cost=122,832.76..1,138,671.00 rows=63,280 width=652) (actual time=100,296.834..100,296.835 rows=0 loops=1)

3. 640.920 4,459.972 ↓ 32.4 49,126 1

Hash Join (cost=79,362.19..185,209.16 rows=1,517 width=148) (actual time=3,790.769..4,459.972 rows=49,126 loops=1)

  • Hash Cond: (o.order_id = f.order_id)
4. 29.861 38.249 ↑ 1.0 100,566 1

Bitmap Heap Scan on trade_order o (cost=2,604.57..108,055.84 rows=101,475 width=112) (actual time=9.072..38.249 rows=100,566 loops=1)

  • Recheck Cond: (("time" >= (to_date('20180101'::text, 'YYYYMMDD'::text) - ('6hour'::cstring)::interval)) AND ("time" <= (to_date('20180424'::text, 'YYYYMMDD'::text) + ('18hour'::cstring)::interval)))
  • Heap Blocks: exact=4461
5. 8.388 8.388 ↑ 1.0 100,566 1

Bitmap Index Scan on trade_order_ind7 (cost=0.00..2,579.20 rows=101,475 width=0) (actual time=8.388..8.388 rows=100,566 loops=1)

  • Index Cond: (("time" >= (to_date('20180101'::text, 'YYYYMMDD'::text) - ('6hour'::cstring)::interval)) AND ("time" <= (to_date('20180424'::text, 'YYYYMMDD'::text) + ('18hour'::cstring)::interval)))
6. 963.483 3,780.803 ↓ 37.2 1,615,436 1

Hash (cost=76,214.91..76,214.91 rows=43,417 width=36) (actual time=3,780.803..3,780.803 rows=1,615,436 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 32 (originally 1) Memory Usage: 3742kB
7. 878.572 2,817.320 ↓ 37.2 1,615,436 1

Gather (cost=1,000.00..76,214.91 rows=43,417 width=36) (actual time=0.541..2,817.320 rows=1,615,436 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 1,938.748 1,938.748 ↓ 29.8 538,479 3 / 3

Parallel Seq Scan on flyallocs f (cost=0.00..70,873.21 rows=18,090 width=36) (actual time=0.031..1,938.748 rows=538,479 loops=3)

  • Filter: ((' '::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)))
  • Rows Removed by Filter: 429677
9. 98.242 95,746.574 ↓ 0.0 0 49,126

Hash Join (cost=43,466.62..43,469.75 rows=1 width=172) (actual time=1.949..1.949 rows=0 loops=49,126)

  • Hash Cond: (cit.instrument_type_id = i.instrument_type_id)
10. 0.010 0.010 ↑ 81.0 1 1

Seq Scan on instrument_type cit (cost=0.00..2.81 rows=81 width=19) (actual time=0.010..0.010 rows=1 loops=1)

11. 49.126 95,648.322 ↓ 0.0 0 49,126

Hash (cost=43,466.61..43,466.61 rows=1 width=157) (actual time=1.947..1.947 rows=0 loops=49,126)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
12. 0.000 95,599.196 ↓ 0.0 0 49,126

Nested Loop (cost=43,377.87..43,466.61 rows=1 width=157) (actual time=1.946..1.946 rows=0 loops=49,126)

13. 98.252 95,599.196 ↓ 0.0 0 49,126

Hash Join (cost=43,373.36..43,455.00 rows=1 width=87) (actual time=1.946..1.946 rows=0 loops=49,126)

  • Hash Cond: (t_1.trader_id = tr.trader_id)
  • Join Filter: ((o.order_id = t.order_id) AND (o.trader_id = t_1.trader_id))
14. 54,382.482 95,255.314 ↓ 0.0 0 49,126

Nested Loop (cost=43,367.97..43,441.10 rows=2,260 width=68) (actual time=1.939..1.939 rows=0 loops=49,126)

15. 20,146.148 40,872.832 ↓ 1,973.0 1,973 49,126

Nested Loop (cost=43,367.97..43,385.90 rows=1 width=68) (actual time=0.071..0.832 rows=1,973 loops=49,126)

16. 294.756 294.756 ↑ 1.0 1 49,126

Index Scan using trade_ind6 on trade t (cost=0.43..14.36 rows=1 width=64) (actual time=0.005..0.006 rows=1 loops=49,126)

  • Index Cond: (order_id = f.order_id)
  • Filter: ((f.fund)::text = (subfund_abbrev)::text)
17. 17,232.581 20,431.928 ↓ 7.5 1,499 64,658

HashAggregate (cost=43,367.54..43,369.54 rows=200 width=4) (actual time=0.050..0.316 rows=1,499 loops=64,658)

  • Group Key: t_1.trader_id
18. 6.436 3,199.347 ↑ 1.0 1,499 1

HashAggregate (cost=43,318.82..43,348.80 rows=1,499 width=110) (actual time=3,198.502..3,199.347 rows=1,499 loops=1)

  • 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. 3,015.480 3,192.911 ↑ 3.4 2,997 1

Nested Loop (cost=0.84..42,961.30 rows=10,215 width=110) (actual time=0.084..3,192.911 rows=2,997 loops=1)

  • 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.549 0.549 ↑ 1.0 1,499 1

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

21. 175.711 176.882 ↓ 2.2 1,499 1,499

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

22. 0.255 1.171 ↓ 2.2 1,499 1

Nested Loop (cost=0.84..1,869.77 rows=683 width=16) (actual time=0.066..1.171 rows=1,499 loops=1)

23. 0.013 0.013 ↑ 1.0 1 1

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

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

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

  • Index Cond: ((user_id = tu.old_tudor_id) AND (applic = 'Orders'::text) AND (client_id = tu.client_id))
  • Heap Fetches: 1499
25. 0.000 0.000 ↓ 0.0 0 96,922,342

Seq Scan on zz_100042200_tidlist ff (cost=0.00..32.60 rows=2,260 width=0) (actual time=0.000..0.000 rows=0 loops=96,922,342)

26. 49.126 245.630 ↑ 1.0 1 49,126

Hash (cost=5.38..5.38 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=49,126)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 196.504 196.504 ↑ 1.0 1 49,126

Index Scan using trader_pk on trader tr (cost=0.28..5.38 rows=1 width=19) (actual time=0.003..0.004 rows=1 loops=49,126)

  • Index Cond: (trader_id = o.trader_id)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using instrument_pk on instrument i (cost=4.51..11.60 rows=1 width=70) (never executed)

  • Index Cond: (tid = o.ins_iid)
  • Filter: ((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))
29.          

SubPlan (for Index Scan)

30. 0.000 0.000 ↓ 0.0 0

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

  • Filter: (companyid = 228)
31.          

SubPlan (for Nested Loop)

32. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..27.40 rows=1 width=4) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using trade_ind15 on trade tt (cost=0.43..10.48 rows=2 width=8) (never executed)

  • Index Cond: ((ps_id = t.ps_id) AND (ps_liquid = 'L'::text))
  • Heap Fetches: 0
34. 0.000 0.000 ↓ 0.0 0

Index Scan using trade_order_ind18 on trade_order oo (cost=0.43..8.45 rows=1 width=4) (never executed)

  • Index Cond: (order_id = tt.order_id)
  • Filter: ((exec_broker)::text ~~ 'ZZ%'::text)
35. 0.000 0.000 ↓ 0.0 0

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

  • Filter: (companyid = 228)