explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XCTS

Settings
# exclusive inclusive rows x rows loops node
1. 724.163 11,599.488 ↓ 24.0 24 1

Unique (cost=527.27..527.28 rows=1 width=1,077) (actual time=10,110.469..11,599.488 rows=24 loops=1)

2.          

CTE orders

3. 0.014 9.492 ↑ 133.1 12 1

Nested Loop (cost=1.82..445.55 rows=1,597 width=495) (actual time=9.258..9.492 rows=12 loops=1)

4. 0.009 9.194 ↑ 100.0 2 1

HashAggregate (cost=1.77..1.97 rows=200 width=16) (actual time=9.192..9.194 rows=2 loops=1)

  • Group Key: fc_get_accounts.id_investor, fc_get_accounts.account_number, fc_get_accounts.id_broker
5. 9.185 9.185 ↑ 500.0 2 1

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

6. 0.284 0.284 ↓ 6.0 6 2

Index Scan using tb_order_idx_query_slobodan_3 on tb_order (cost=0.04..2.22 rows=1 width=495) (actual time=0.029..0.142 rows=6 loops=2)

  • Index Cond: ((account = fc_get_accounts.account_number) AND (id_broker = fc_get_accounts.id_broker))
  • Filter: ((fc_get_accounts.id_investor = id_investor) AND ((dh_last_update > '1900-01-01 00:00:00'::timestamp without time zone) OR (cd_order_status = ANY ('{PEND,AGUA,PARC,PROC,ALTE,SCAN,SALT}'::bpchar[]))))
  • Rows Removed by Filter: 128
7. 8,045.794 10,875.325 ↓ 317,952.0 317,952 1

Sort (cost=81.72..81.72 rows=1 width=1,077) (actual time=10,110.468..10,875.325 rows=317,952 loops=1)

  • Sort Key: (to_char(orders.dh_last_update, 'YYYYMMDDHH24MISS'::text)) DESC, orders.id_order, orders.account, orders.id_investor, (CASE WHEN (orders.id_operator <> orders.id_investor) THEN COALESCE(orders.id_operator, 0) ELSE 0 END), orders.id_broker, orders.id_exchange, (CASE WHEN ((orders.cd_order_type = '4'::bpchar) AND (orders.personalized_gain_trigger IS NOT NULL) AND (orders.id_broker = ANY ('{7,13}'::integer[]))) THEN 'D'::bpchar ELSE orders.cd_order_type END), orders.side, orders.id_stock, orders.qtty, orders.qtty_executed, orders.qtty_left, orders.qtty_mim, orders.max_floor, orders.price, orders.cd_order_time_enforce, orders.dt_expirate_date, orders.hr_expirate_date, (to_char(orders.dh_creation_time, 'YYYYMMDDHH24MISS'::text)), orders.id_order_fix_server, orders.id_order_exchange, orders.cd_order_status, orders.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 ((orders.cd_order_type = '4'::bpchar) AND (orders.personalized_gain_trigger IS NOT NULL) AND (orders.id_broker = ANY ('{7,13}'::integer[]))) THEN 'Start/Stop Duplo'::character varying ELSE tb_order_type.ds_order_type END), orders.price_average, (COALESCE(tb_order_event.cd_order_reject_reason, '-1'::integer)), 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)), orders.last_price, (COALESCE(tb_stock.tick_size_denominator, 2)), (COALESCE(orders.personalized_stop_type, ''::bpchar)), (COALESCE(orders.personalized_gain_trigger, '-1'::numeric)), (COALESCE(orders.personalized_gain_price, '-1'::numeric)), (COALESCE(orders.personalized_loss_trigger, '-1'::numeric)), (COALESCE(orders.personalized_loss_price, '-1'::numeric)), (COALESCE(orders.personalized_stop_triggered, false)), (COALESCE(orders.personalized_stop_triggered_side, ''::bpchar)), (COALESCE(orders.personalized_new_order_scheduled, false)), (COALESCE(orders.personalized_cancel_replace_scheduled, false)), (COALESCE(tb_order_reject_reason.ds_order_reject_reason, ''::character varying)), (COALESCE(tb_order_event.ds_text, ''::character varying)), (COALESCE(CASE WHEN (tb_stock.cd_segment = '9999'::bpchar) THEN (((((orders.qtty_left)::numeric * orders.price) + ((orders.qtty_executed)::numeric * orders.price_average)) * tb_stock.vl_contract_multiplier))::double precision ELSE ((((orders.price * (orders.qtty_left)::numeric) + ((orders.qtty_executed)::numeric * orders.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, orders.dh_last_update)) * '1000'::double precision))::character varying(20)), (CASE WHEN (orders.cd_order_status = 'CANC'::bpchar) THEN (orders.qtty - orders.qtty_executed) ELSE '0'::bigint END), orders.id_algo_request, orders.id_original_bolsa
  • Sort Method: external merge Disk: 117192kB
8. 1,478.621 2,829.531 ↓ 317,952.0 317,952 1

Nested Loop (cost=7.59..81.72 rows=1 width=1,077) (actual time=9.832..2,829.531 rows=317,952 loops=1)

9. 166.547 1,032.958 ↓ 317,952.0 317,952 1

Nested Loop (cost=7.58..81.69 rows=1 width=726) (actual time=9.773..1,032.958 rows=317,952 loops=1)

10. 174.436 548.459 ↓ 317,952.0 317,952 1

Nested Loop (cost=7.56..81.68 rows=1 width=719) (actual time=9.760..548.459 rows=317,952 loops=1)

  • Join Filter: ((tb_broker.id_broker = tb_investor_broker_account.id_broker) AND (tb_contract_broker.id_contract = tb_investor_broker_account.id_contract))
  • Rows Removed by Join Filter: 317952
11. 0.074 10.927 ↓ 24.0 24 1

Nested Loop (cost=7.52..80.05 rows=1 width=721) (actual time=9.737..10.927 rows=24 loops=1)

12. 0.081 10.757 ↓ 24.0 24 1

Nested Loop (cost=7.51..80.03 rows=1 width=711) (actual time=9.721..10.757 rows=24 loops=1)

13. 0.082 10.532 ↓ 1.4 24 1

Merge Join (cost=7.49..79.74 rows=17 width=699) (actual time=9.705..10.532 rows=24 loops=1)

  • Merge Cond: (tb_broker.id_broker = tb_contract_broker.id_broker)
14. 0.040 10.402 ↑ 1.3 12 1

Nested Loop (cost=6.42..95.30 rows=16 width=691) (actual time=9.672..10.402 rows=12 loops=1)

15. 0.045 10.302 ↑ 1.3 12 1

Nested Loop Left Join (cost=6.40..94.99 rows=16 width=667) (actual time=9.658..10.302 rows=12 loops=1)

  • Join Filter: (orders.id_operator <> orders.id_investor)
  • Rows Removed by Join Filter: 12
16. 0.067 10.149 ↑ 1.3 12 1

Nested Loop Left Join (cost=6.36..66.58 rows=16 width=608) (actual time=9.643..10.149 rows=12 loops=1)

17. 0.030 9.962 ↑ 1.3 12 1

Nested Loop Left Join (cost=6.32..36.20 rows=16 width=581) (actual time=9.625..9.962 rows=12 loops=1)

18. 0.048 9.884 ↑ 1.3 12 1

Nested Loop Left Join (cost=6.29..34.96 rows=16 width=539) (actual time=9.622..9.884 rows=12 loops=1)

  • Join Filter: (orders.cd_order_status = 'REJE'::bpchar)
19. 0.042 9.644 ↑ 1.3 12 1

Merge Join (cost=6.25..6.35 rows=16 width=505) (actual time=9.602..9.644 rows=12 loops=1)

  • Merge Cond: (orders.id_broker = tb_broker.id_broker)
20. 0.023 9.579 ↑ 12.0 12 1

Sort (cost=5.18..5.21 rows=144 width=481) (actual time=9.571..9.579 rows=12 loops=1)

  • Sort Key: orders.id_broker
  • Sort Method: quicksort Memory: 28kB
21. 0.020 9.556 ↑ 12.0 12 1

Hash Join (cost=1.04..4.66 rows=144 width=481) (actual time=9.293..9.556 rows=12 loops=1)

  • Hash Cond: (orders.cd_order_status = tb_order_status.cd_order_status)
22. 9.521 9.521 ↑ 133.1 12 1

CTE Scan on orders (cost=0.00..3.19 rows=1,597 width=465) (actual time=9.267..9.521 rows=12 loops=1)

23. 0.006 0.015 ↑ 1.0 18 1

Hash (cost=1.02..1.02 rows=18 width=21) (actual time=0.015..0.015 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.009 0.009 ↑ 1.0 18 1

Seq Scan on tb_order_status (cost=0.00..1.02 rows=18 width=21) (actual time=0.005..0.009 rows=18 loops=1)

25. 0.011 0.023 ↑ 1.3 17 1

Sort (cost=1.07..1.08 rows=22 width=24) (actual time=0.022..0.023 rows=17 loops=1)

  • Sort Key: tb_broker.id_broker
  • Sort Method: quicksort Memory: 26kB
26. 0.012 0.012 ↑ 1.0 22 1

Seq Scan on tb_broker (cost=0.00..1.02 rows=22 width=24) (actual time=0.005..0.012 rows=22 loops=1)

27. 0.192 0.192 ↓ 0.0 0 12

Index Scan using tb_order_event_id_order_cd_order_status_qtty_last_dh_creati_idx on tb_order_event (cost=0.04..1.79 rows=1 width=42) (actual time=0.015..0.016 rows=0 loops=12)

  • Index Cond: ((orders.id_order = id_order) AND (cd_order_status = 'REJE'::bpchar))
28. 0.048 0.048 ↓ 0.0 0 12

Index Scan using tb_order_reject_reason_pk on tb_order_reject_reason (cost=0.03..0.08 rows=1 width=46) (actual time=0.004..0.004 rows=0 loops=12)

  • Index Cond: (tb_order_event.cd_order_reject_reason = cd_order_reject_reason)
29. 0.120 0.120 ↑ 1.0 1 12

Index Scan using tb_stock_pk on tb_stock (cost=0.04..1.90 rows=1 width=31) (actual time=0.010..0.010 rows=1 loops=12)

  • Index Cond: (id_stock = orders.id_stock)
30. 0.108 0.108 ↑ 1.0 1 12

Index Scan using tb_investor_pk on tb_investor (cost=0.04..1.77 rows=1 width=63) (actual time=0.009..0.009 rows=1 loops=12)

  • Index Cond: (id_investor = orders.id_operator)
31. 0.060 0.060 ↑ 1.0 1 12

Index Scan using tb_exchange_pk on tb_exchange (cost=0.01..0.02 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=12)

  • Index Cond: (id_exchange = orders.id_exchange)
32. 0.043 0.048 ↓ 2.0 45 1

Sort (cost=1.08..1.08 rows=23 width=8) (actual time=0.025..0.048 rows=45 loops=1)

  • Sort Key: tb_contract_broker.id_broker
  • Sort Method: quicksort Memory: 26kB
33. 0.005 0.005 ↑ 1.0 23 1

Seq Scan on tb_contract_broker (cost=0.00..1.02 rows=23 width=8) (actual time=0.004..0.005 rows=23 loops=1)

34. 0.144 0.144 ↑ 1.0 1 24

Index Scan using tb_order_time_enforce_pk on tb_order_time_enforce (cost=0.01..0.02 rows=1 width=14) (actual time=0.006..0.006 rows=1 loops=24)

  • Index Cond: (cd_order_time_enforce = orders.cd_order_time_enforce)
35. 0.096 0.096 ↑ 1.0 1 24

Index Scan using tb_order_type_pk on tb_order_type (cost=0.01..0.02 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=24)

  • Index Cond: (cd_order_type = orders.cd_order_type)
36. 363.096 363.096 ↓ 26,496.0 26,496 24

Index Scan using tb_investor_broker_account_idx0 on tb_investor_broker_account (cost=0.04..1.62 rows=1 width=18) (actual time=0.013..15.129 rows=26,496 loops=24)

  • Index Cond: ((id_broker = orders.id_broker) AND (account_number = orders.account))
37. 317.952 317.952 ↑ 1.0 1 317,952

Index Scan using tb_investor_broker_account_type_pk on tb_investor_broker_account_type (cost=0.01..0.01 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=317,952)

  • Index Cond: (tp_account = tb_investor_broker_account.tp_account)
38. 317.952 317.952 ↑ 1.0 1 317,952

Index Only Scan using tb_contract_pk on tb_contract (cost=0.01..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=317,952)

  • Index Cond: (id_contract = tb_investor_broker_account.id_contract)
  • Heap Fetches: 0