PostgreSQL join fetch all rows in table, too slow -

PostgreSQL join fetch all rows in table, too slow -

i have 2 tables "commissions" , "mt4_trades". in "mt4_trades" "ticket" column private key, in "commissions" there "order_id" , has relation mt4_trades.ticket 1 many (one "ticket" many "order_id"). , have statement:

select commissions.ibs_account ibs_account "public"."mt4_trades" inner bring together commissions on commissions.order_id = mt4_trades.ticket "mt4_trades"."close_time" >= '2014.11.01' , "mt4_trades"."close_time" < '2014.12.01'

commissions table constains 4 millions rows. statement homecoming 480000 rows. slow: executions time 9 sec. did explain analyze:

hash bring together (cost=43397.07..216259.97 rows=144233 width=7) (actual time=3993.839..9459.896 rows=488131 loops=1) hash cond: (commissions.order_id = mt4_trades.ticket) -> seq scan on commissions (cost=0.00..116452.08 rows=3997708 width=15) (actual time=0.005..4185.254 rows=3997157 loops=1) -> hash (cost=42485.10..42485.10 rows=72958 width=4) (actual time=288.767..288.767 rows=97260 loops=1) buckets: 8192 batches: 1 memory usage: 3420kb -> index scan using "index_closetime" on mt4_trades (cost=0.43..42485.10 rows=72958 width=4) (actual time=0.020..174.810 rows=97260 loops=1) index cond: ((close_time >= '2014-11-01 00:00:00'::timestamp without time zone) , (close_time < '2014-12-01 00:00:00'::timestamp without time zone)) total runtime: 9881.979 ms

this row:

-> seq scan on commissions (cost=0.00..116452.08 rows=3997708 width=15) (actual time=0.005..4185.254 rows=3997157 loops=1)

means scan whole "commissions" table instead compare "order_id" , "ticket" first. can help me how can improve query. thanks

9 seconds homecoming half 1000000 rows not terrible, , sequential scan on 4m much faster faster 100k indexed lookups on 4m. assuming you've got index on order_id, can test running set enable_seqscan false; before running query (this impact current connection).

do need 500k rows every time run query? or going filtering results? if you're going filtering results in other way, want optimize against query rather 1 returns 500k rows.

postgresql inner-join query-performance postgresql-performance