explain analyze select t.tb_order_number, f.account_number, f.clearing_account_id, f.symbol_full_name, f.fill_quantity, f.buy_or_sell, f.trader_username, f.fill_price, f.fill_quantity, f.fill_ts, f.last_update_ts, s.symbol_type, s.base_symbol_full_name, s.symbol_name, ca.clearing_account_number, ca.clearing_firm, co.tick_value from fill f, tb_order t, symbol s, clearing_account ca, contract co where f.fill_ts > '2002-02-04 00:00:00' and f.fill_ts <= '2002-02-06 23:59:59' and t.order_id = f.order_id and s.full_name = f.symbol_full_name and co.contract_name = s.contract_name and ca.clearing_account_id = f.clearing_account_id and s.symbol_name not in ('ISLD:TESTA', 'ISLD:TESTB', 'ISLD:TESTC'); ----------------------------------------------------------------------------- using csquared = fabs(indexCorrelation); f.fill_ts > '2002-02-04 00:00:00' and f.fill_ts <= '2002-02-04 23:59:59' and Hash Join (cost=35.98..250269.85 rows=25830 width=170) (actual time=251.27..4578.73 rows=48633 loops=1) -> Nested Loop (cost=34.26..249816.11 rows=25830 width=146) (actual time=235.52..4193.11 rows=48633 loops=1) -> Hash Join (cost=34.26..107159.54 rows=25830 width=128) (actual time=141.91..2035.45 rows=48633 loops=1) -> Index Scan using fill_ak2 on fill f (cost=0.00..106619.81 rows=36518 width=66) (actual time=125.70..1645.10 rows=48633 loops=1) -> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=16.16..16.16 rows=0 loops=1) -> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=10.79..15.26 rows=559 loops=1) -> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.07..2.40 rows=559 loops=1) -> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=10.67..10.67 rows=0 loops=1) -> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=10.62..10.64 rows=13 loops=1) -> Index Scan using order_pk on tb_order t (cost=0.00..5.51 rows=1 width=18) (actual time=0.03..0.04 rows=1 loops=48633) -> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=15.68..15.68 rows=0 loops=1) -> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=15.52..15.61 rows=57 loops=1) Total runtime: 4599.75 msec ----------------------------------------------------------------------------- using csquared = fabs(indexCorrelation); f.fill_ts > '2002-02-04 00:00:00' and f.fill_ts <= '2002-02-05 23:59:59' and Hash Join (cost=35.98..495564.76 rows=51152 width=170) (actual time=6.41..6418.32 rows=113026 loops=1) -> Nested Loop (cost=34.26..494667.89 rows=51152 width=146) (actual time=6.13..5555.21 rows=113026 loops=1) -> Hash Join (cost=34.26..212165.66 rows=51152 width=128) (actual time=5.99..2435.00 rows=113026 loops=1) -> Index Scan using fill_ak2 on fill f (cost=0.00..211130.42 rows=72316 width=66) (actual time=0.27..1559.79 rows=113026 loops=1) -> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=5.67..5.67 rows=0 loops=1) -> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=0.21..4.72 rows=559 loops=1) -> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.05..2.37 rows=559 loops=1) -> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=0.10..0.10 rows=0 loops=1) -> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=0.05..0.07 rows=13 loops=1) -> Index Scan using order_pk on tb_order t (cost=0.00..5.51 rows=1 width=18) (actual time=0.02..0.02 rows=1 loops=113026) -> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.23..0.23 rows=0 loops=1) -> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=0.07..0.16 rows=57 loops=1) Total runtime: 6465.18 msec ----------------------------------------------------------------------------- using csquared = fabs(indexCorrelation); f.fill_ts > '2002-02-04 00:00:00' and f.fill_ts <= '2002-02-06 23:59:59' and Nested Loop (cost=35.98..739742.66 rows=76473 width=170) (actual time=49118.56..130890.07 rows=191483 loops=1) -> Hash Join (cost=35.98..317394.77 rows=76473 width=152) (actual time=49067.80..125663.26 rows=191483 loops=1) -> Hash Join (cost=34.26..316054.79 rows=76473 width=128) (actual time=49067.51..124002.64 rows=191483 loops=1) -> Seq Scan on fill f (cost=0.00..314524.04 rows=108115 width=66) (actual time=49061.88..122556.03 rows=191483 loops=1) -> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=5.57..5.57 rows=0 loops=1) -> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=0.19..4.67 rows=559 loops=1) -> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.05..2.37 rows=559 loops=1) -> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=0.09..0.09 rows=0 loops=1) -> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=0.04..0.06 rows=13 loops=1) -> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.23..0.23 rows=0 loops=1) -> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=0.06..0.15 rows=57 loops=1) -> Index Scan using order_pk on tb_order t (cost=0.00..5.51 rows=1 width=18) (actual time=0.02..0.02 rows=1 loops=191483) Total runtime: 130973.82 msec ----------------------------------------------------------------------------- ----------------------------------------------------------------------------- ----------------------------------------------------------------------------- csquared = indexCorrelation * indexCorrelation; f.fill_ts > '2002-02-04 00:00:00' and f.fill_ts <= '2002-02-04 23:59:59' and Nested Loop (cost=35.98..289773.54 rows=25830 width=170) (actual time=184.26..2418.14 rows=48633 loops=1) -> Hash Join (cost=35.98..136300.26 rows=25830 width=152) (actual time=139.78..1340.42 rows=48633 loops=1) -> Hash Join (cost=34.26..135846.52 rows=25830 width=128) (actual time=124.06..901.60 rows=48633 loops=1) -> Index Scan using fill_ak2 on fill f (cost=0.00..135306.79 rows=36518 width=66) (actual time=107.86..509.52 rows=48633 loops=1) -> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=16.13..16.13 rows=0 loops=1) -> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=10.79..15.23 rows=559 loops=1) -> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.07..2.40 rows=559 loops=1) -> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=10.67..10.67 rows=0 loops=1) -> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=10.63..10.64 rows=13 loops=1) -> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=15.67..15.67 rows=0 loops=1) -> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=15.50..15.59 rows=57 loops=1) -> Index Scan using order_pk on tb_order t (cost=0.00..5.93 rows=1 width=18) (actual time=0.01..0.01 rows=1 loops=48633) Total runtime: 2439.64 msec ----------------------------------------------------------------------------- csquared = indexCorrelation * indexCorrelation; f.fill_ts > '2002-02-04 00:00:00' and f.fill_ts <= '2002-02-05 23:59:59' and Nested Loop (cost=35.98..573792.17 rows=51152 width=170) (actual time=6.23..6919.29 rows=113026 loops=1) -> Hash Join (cost=35.98..269869.65 rows=51152 width=152) (actual time=6.09..3712.27 rows=113026 loops=1) -> Hash Join (cost=34.26..268972.79 rows=51152 width=128) (actual time=5.81..2727.98 rows=113026 loops=1) -> Index Scan using fill_ak2 on fill f (cost=0.00..267937.55 rows=72316 width=66) (actual time=0.27..1848.67 rows=113026 loops=1) -> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=5.49..5.49 rows=0 loops=1) -> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=0.19..4.59 rows=559 loops=1) -> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.06..2.35 rows=559 loops=1) -> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=0.09..0.09 rows=0 loops=1) -> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=0.04..0.06 rows=13 loops=1) -> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.23..0.23 rows=0 loops=1) -> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=0.07..0.16 rows=57 loops=1) -> Index Scan using order_pk on tb_order t (cost=0.00..5.93 rows=1 width=18) (actual time=0.02..0.02 rows=1 loops=113026) Total runtime: 6967.91 msec ----------------------------------------------------------------------------- csquared = indexCorrelation * indexCorrelation; f.fill_ts > '2002-02-04 00:00:00' and f.fill_ts <= '2002-02-06 23:59:59' and Nested Loop (cost=35.98..771766.53 rows=76473 width=170) (actual time=49185.87..131460.79 rows=191483 loops=1) -> Hash Join (cost=35.98..317394.77 rows=76473 width=152) (actual time=49135.04..126128.47 rows=191483 loops=1) -> Hash Join (cost=34.26..316054.79 rows=76473 width=128) (actual time=49134.75..124465.11 rows=191483 loops=1) -> Seq Scan on fill f (cost=0.00..314524.04 rows=108115 width=66) (actual time=49129.08..123019.27 rows=191483 loops=1) -> Hash (cost=32.89..32.89 rows=547 width=62) (actual time=5.60..5.60 rows=0 loops=1) -> Hash Join (cost=1.16..32.89 rows=547 width=62) (actual time=0.19..4.68 rows=559 loops=1) -> Seq Scan on symbol s (cost=0.00..20.78 rows=547 width=48) (actual time=0.05..2.38 rows=559 loops=1) -> Hash (cost=1.13..1.13 rows=13 width=14) (actual time=0.09..0.09 rows=0 loops=1) -> Seq Scan on contract co (cost=0.00..1.13 rows=13 width=14) (actual time=0.04..0.06 rows=13 loops=1) -> Hash (cost=1.57..1.57 rows=57 width=24) (actual time=0.23..0.23 rows=0 loops=1) -> Seq Scan on clearing_account ca (cost=0.00..1.57 rows=57 width=24) (actual time=0.07..0.15 rows=57 loops=1) -> Index Scan using order_pk on tb_order t (cost=0.00..5.93 rows=1 width=18) (actual time=0.02..0.02 rows=1 loops=191483) Total runtime: 131542.17 msec