explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WjR4

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 7,647.803 ↓ 0.0 0 1

Limit (cost=78,195.42..78,195.43 rows=1 width=924) (actual time=7,647.803..7,647.803 rows=0 loops=1)

2.          

CTE accounts

3. 36.461 36.461 ↑ 1,000.0 1 1

Function Scan on fc_get_accounts (cost=0.03..1.02 rows=1,000 width=16) (actual time=36.461..36.461 rows=1 loops=1)

4. 0.034 7,647.802 ↓ 3.0 3 1

Unique (cost=78,194.38..78,194.39 rows=1 width=924) (actual time=7,647.771..7,647.802 rows=3 loops=1)

5. 0.174 7,647.768 ↓ 3.0 3 1

Sort (cost=78,194.38..78,194.38 rows=1 width=924) (actual time=7,647.762..7,647.768 rows=3 loops=1)

  • Sort Key: (to_char(tb_order_hist.dh_last_update, 'YYYYMMDDHH24MISS'::text)) DESC, tb_order_hist.id_order, tb_order_hist.account, tb_order_hist.id_investor, (CASE WHEN (tb_order_hist.id_operator <> tb_order_hist.id_investor) THEN COALESCE(tb_order_hist.id_operator, 0) ELSE 0 END), tb_order_hist.id_broker, tb_order_hist.id_exchange, (CASE WHEN ((tb_order_hist.cd_order_type = '4'::bpchar) AND (tb_order_hist.personalized_gain_trigger IS NOT NULL) AND (tb_order_hist.id_broker = 7)) THEN 'D'::bpchar ELSE tb_order_hist.cd_order_type END), tb_order_hist.side, tb_order_hist.id_stock, tb_order_hist.qtty, tb_order_hist.qtty_executed, tb_order_hist.qtty_left, tb_order_hist.qtty_mim, tb_order_hist.max_floor, tb_order_hist.price, tb_order_hist.cd_order_time_enforce, tb_order_hist.dt_expirate_date, tb_order_hist.hr_expirate_date, (to_char(tb_order_hist.dh_creation_time, 'YYYYMMDDHH24MISS'::text)), tb_order_hist.id_order_fix_server, tb_order_hist.id_order_exchange, tb_order_hist.cd_order_status, tb_order_hist.price_stop, (COALESCE(tb_stock.cd_stock, '0'::character varying)), (COALESCE(tb_order_status.ds_order_status, '0'::character varying)), (COALESCE(tb_broker.nm_broker, '0'::character varying)), (COALESCE(tb_exchange.ds_exchange, '0'::character varying)), (COALESCE(tb_order_time_enforce.ds_order_time_enforce, '0'::character varying)), (CASE WHEN ((tb_order_hist.cd_order_type = '4'::bpchar) AND (tb_order_hist.personalized_gain_trigger IS NOT NULL) AND (tb_order_hist.id_broker = 7)) THEN 'Start/Stop Duplo'::character varying ELSE tb_order_type.ds_order_type END), tb_order_hist.price_average, (CASE WHEN (tb_order_hist.cd_order_status = 'REJE'::bpchar) THEN (SubPlan 2) ELSE '-1'::integer END), tb_broker.nm_broker_mnemonic, tb_investor_broker_account_type.ds_account_type, (COALESCE(tb_investor.name, ''::character varying)), (COALESCE(tb_investor.cd_user, ''::character varying)), tb_order_hist.last_price, (COALESCE(tb_stock.tick_size_denominator, 2)), (COALESCE(tb_order_hist.personalized_stop_type, ''::bpchar)), (COALESCE(tb_order_hist.personalized_gain_trigger, '-1'::numeric)), (COALESCE(tb_order_hist.personalized_gain_price, '-1'::numeric)), (COALESCE(tb_order_hist.personalized_loss_trigger, '-1'::numeric)), (COALESCE(tb_order_hist.personalized_loss_price, '-1'::numeric)), (COALESCE(tb_order_hist.personalized_stop_triggered, false)), (COALESCE(tb_order_hist.personalized_stop_triggered_side, ''::bpchar)), (COALESCE(tb_order_hist.personalized_new_order_scheduled, false)), (COALESCE(tb_order_hist.personalized_cancel_replace_scheduled, false)), (CASE WHEN (tb_order_hist.cd_order_status = 'REJE'::bpchar) THEN COALESCE((SubPlan 3), ''::character varying) ELSE ''::character varying END), (CASE WHEN (tb_order_hist.cd_order_status = 'REJE'::bpchar) THEN COALESCE((SubPlan 4), ''::character varying) ELSE ''::character varying END), (COALESCE(CASE WHEN (tb_stock.cd_segment = '9999'::bpchar) THEN (((((tb_order_hist.qtty_left)::numeric * tb_order_hist.price) + ((tb_order_hist.qtty_executed)::numeric * tb_order_hist.price_average)) * tb_stock.vl_contract_multiplier))::double precision ELSE ((((tb_order_hist.price * (tb_order_hist.qtty_left)::numeric) + ((tb_order_hist.qtty_executed)::numeric * tb_order_hist.price_average)))::double precision * pow('10'::double precision, ((1 - tb_stock.quotation_form))::double precision)) END, '-1'::double precision)), tb_stock.vl_contract_multiplier, tb_stock.quotation_form, (((date_part('epoch'::text, date_trunc('milliseconds'::text, tb_order_hist.dh_last_update)) * '1000'::double precision))::character varying(20)), (CASE WHEN (tb_order_hist.cd_order_status = 'CANC'::bpchar) THEN (tb_order_hist.qtty - tb_order_hist.qtty_executed) ELSE '0'::bigint END), tb_order_hist.id_algo_request
  • Sort Method: quicksort Memory: 26kB
6. 0.364 7,647.594 ↓ 3.0 3 1

Nested Loop (cost=9,069.86..78,194.38 rows=1 width=924) (actual time=3,787.796..7,647.594 rows=3 loops=1)

  • Join Filter: (tb_investor_broker_account.tp_account = tb_investor_broker_account_type.tp_account)
  • Rows Removed by Join Filter: 9
7. 300.461 7,647.206 ↓ 3.0 3 1

Hash Join (cost=9,069.86..78,179.85 rows=1 width=414) (actual time=3,787.570..7,647.206 rows=3 loops=1)

  • Hash Cond: ((tb_broker.id_broker = tb_investor_broker_account.id_broker) AND (tb_order_hist.account = tb_investor_broker_account.account_number) AND (tb_order_hist.id_investor = accounts.id_investor))
8. 381.410 7,310.254 ↑ 1.7 1,180,335 1

Nested Loop (cost=8,680.60..75,472.98 rows=2,060,100 width=416) (actual time=652.801..7,310.254 rows=1,180,335 loops=1)

  • Join Filter: (tb_order_hist.id_broker = tb_broker.id_broker)
9. 0.023 0.023 ↑ 1.0 1 1

Index Scan using tb_broker_pk on tb_broker (cost=0.01..0.41 rows=1 width=61) (actual time=0.007..0.023 rows=1 loops=1)

10. 595.644 6,928.821 ↑ 1.7 1,180,335 1

Hash Join (cost=8,680.59..72,897.44 rows=2,060,100 width=355) (actual time=652.790..6,928.821 rows=1,180,335 loops=1)

  • Hash Cond: (tb_order_hist.cd_order_type = tb_order_type.cd_order_type)
11. 351.706 6,333.163 ↑ 1.7 1,180,335 1

Nested Loop (cost=8,680.37..70,064.58 rows=2,060,100 width=345) (actual time=652.759..6,333.163 rows=1,180,335 loops=1)

  • Join Filter: (tb_order_hist.id_exchange = tb_exchange.id_exchange)
12. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on tb_exchange (cost=0.00..0.20 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=1)

13. 1,013.710 5,981.452 ↑ 1.7 1,180,335 1

Hash Left Join (cost=8,680.37..67,489.26 rows=2,060,100 width=337) (actual time=652.749..5,981.452 rows=1,180,335 loops=1)

  • Hash Cond: (tb_order_hist.id_operator = tb_investor.id_investor)
  • Join Filter: (tb_order_hist.id_operator <> tb_order_hist.id_investor)
  • Rows Removed by Join Filter: 1,179,719
14. 587.032 4,602.921 ↑ 1.7 1,180,335 1

Hash Join (cost=3,641.18..59,102.41 rows=2,060,100 width=274) (actual time=285.314..4,602.921 rows=1,180,335 loops=1)

  • Hash Cond: (tb_order_hist.cd_order_status = tb_order_status.cd_order_status)
15. 653.287 4,015.873 ↑ 1.7 1,180,335 1

Hash Join (cost=3,640.94..56,269.53 rows=2,060,100 width=258) (actual time=285.288..4,015.873 rows=1,180,335 loops=1)

  • Hash Cond: (tb_order_hist.id_stock = tb_stock.id_stock)
16. 732.297 3,078.967 ↑ 1.7 1,180,335 1

Hash Join (cost=0.22..49,796.17 rows=2,060,100 width=230) (actual time=0.038..3,078.967 rows=1,180,335 loops=1)

  • Hash Cond: (tb_order_hist.cd_order_time_enforce = tb_order_time_enforce.cd_order_time_enforce)
17. 2,346.659 2,346.659 ↑ 1.7 1,180,850 1

Seq Scan on tb_order_hist (cost=0.00..46,962.00 rows=2,063,608 width=218) (actual time=0.014..2,346.659 rows=1,180,850 loops=1)

  • Filter: (((dh_last_update >= '2020-04-17 00:00:00'::timestamp without time zone) AND (dh_last_update <= '2020-06-24 23:59:59.999'::timestamp without time zone)) OR ((dh_creation_time >= '2020-04-17 00:00:00'::timestamp without time zone) AND (dh_creation_time <= '2020-06-24 23:59:59.999'::timestamp without time zone)))
  • Rows Removed by Filter: 3,849,349
18. 0.005 0.011 ↑ 1.0 7 1

Hash (cost=0.21..0.21 rows=7 width=14) (actual time=0.011..0.011 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.006 0.006 ↑ 1.0 7 1

Seq Scan on tb_order_time_enforce (cost=0.00..0.21 rows=7 width=14) (actual time=0.005..0.006 rows=7 loops=1)

20. 104.345 283.619 ↓ 1.0 248,587 1

Hash (cost=3,330.43..3,330.43 rows=248,234 width=32) (actual time=283.619..283.619 rows=248,587 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 17,859kB
21. 179.274 179.274 ↓ 1.0 248,587 1

Seq Scan on tb_stock (cost=0.00..3,330.43 rows=248,234 width=32) (actual time=0.014..179.274 rows=248,587 loops=1)

22. 0.006 0.016 ↑ 1.0 19 1

Hash (cost=0.22..0.22 rows=19 width=21) (actual time=0.016..0.016 rows=19 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.010 0.010 ↑ 1.0 19 1

Seq Scan on tb_order_status (cost=0.00..0.22 rows=19 width=21) (actual time=0.006..0.010 rows=19 loops=1)

24. 183.533 364.821 ↓ 1.0 429,061 1

Hash (cost=4,503.86..4,503.86 rows=428,260 width=67) (actual time=364.821..364.821 rows=429,061 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 46,678kB
25. 181.288 181.288 ↓ 1.0 429,061 1

Seq Scan on tb_investor (cost=0.00..4,503.86 rows=428,260 width=67) (actual time=0.009..181.288 rows=429,061 loops=1)

26. 0.004 0.014 ↑ 1.0 8 1

Hash (cost=0.21..0.21 rows=8 width=12) (actual time=0.014..0.014 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.010 0.010 ↑ 1.0 8 1

Seq Scan on tb_order_type (cost=0.00..0.21 rows=8 width=12) (actual time=0.008..0.010 rows=8 loops=1)

28. 0.006 36.491 ↑ 5.0 1 1

Hash (cost=389.25..389.25 rows=5 width=30) (actual time=36.491..36.491 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
29. 0.009 36.485 ↑ 5.0 1 1

Nested Loop (cost=0.04..389.25 rows=5 width=30) (actual time=36.484..36.485 rows=1 loops=1)

30. 36.465 36.465 ↑ 1,000.0 1 1

CTE Scan on accounts (cost=0.00..2.00 rows=1,000 width=16) (actual time=36.464..36.465 rows=1 loops=1)

31. 0.011 0.011 ↑ 1.0 1 1

Index Scan using tb_investor_broker_account_idx0 on tb_investor_broker_account (cost=0.04..0.39 rows=1 width=14) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: ((id_broker = accounts.id_broker) AND (account_number = accounts.account))
32. 0.024 0.024 ↑ 1.0 4 3

Seq Scan on tb_investor_broker_account_type (cost=0.00..0.20 rows=4 width=11) (actual time=0.007..0.008 rows=4 loops=3)

33.          

SubPlan (for Nested Loop)

34. 0.000 0.000 ↓ 0.0 0

Limit (cost=3.07..3.07 rows=1 width=6) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Sort (cost=3.07..3.08 rows=23 width=6) (never executed)

  • Sort Key: tb_order_event_hist.seq
36. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_tb_order_event_hist_idx0 on tb_order_event_hist (cost=0.06..3.06 rows=23 width=6) (never executed)

  • Index Cond: (id_order = tb_order_hist.id_order)
  • Filter: (cd_order_status = tb_order_hist.cd_order_status)
37. 0.000 0.000 ↓ 0.0 0

Limit (cost=8.16..8.16 rows=1 width=44) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Sort (cost=8.16..8.16 rows=1 width=44) (never executed)

  • Sort Key: tb_order_event_hist_1.seq
39. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.08..8.16 rows=1 width=44) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_tb_order_event_hist_idx0 on tb_order_event_hist tb_order_event_hist_1 (cost=0.06..3.06 rows=23 width=6) (never executed)

  • Index Cond: (id_order = tb_order_hist.id_order)
  • Filter: (cd_order_status = tb_order_hist.cd_order_status)
41. 0.000 0.000 ↓ 0.0 0

Index Scan using tb_order_reject_reason_pk on tb_order_reject_reason (cost=0.03..0.22 rows=1 width=46) (never executed)

  • Index Cond: (cd_order_reject_reason = tb_order_event_hist_1.cd_order_reject_reason)
42. 0.000 0.000 ↓ 0.0 0

Limit (cost=3.07..3.07 rows=1 width=81) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Sort (cost=3.07..3.08 rows=23 width=81) (never executed)

  • Sort Key: tb_order_event_hist_2.seq
44. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_tb_order_event_hist_idx0 on tb_order_event_hist tb_order_event_hist_2 (cost=0.06..3.06 rows=23 width=81) (never executed)

  • Index Cond: (id_order = tb_order_hist.id_order)
  • Filter: (cd_order_status = tb_order_hist.cd_order_status)
Planning time : 15.056 ms
Execution time : 7,651.211 ms