rendered paste bodySELECT fxinterval.name, fxccypairmap.ccypair, fxhistorical.low,
fxhistorical.high, fxhistorical.open, fxhistorical.ival_start,
fxhistorical."close"
FROM fxhistorical
JOIN fxccypairmap ON fxhistorical.ccyindex = fxccypairmap.ccyindex
JOIN fxinterval ON fxhistorical.intervalid = fxinterval.intervalid
WHERE fxccypairmap.ccypair='EUR/USD' AND fxinterval.name='1MIN'
ORDER BY fxhistorical.ival_start DESC
LIMIT 500
EXPLAIN OUTPUT:
Limit (cost=2.69..72238.08 rows=500 width=71)
-> Nested Loop (cost=2.69..1490796.75 rows=10319 width=71)
Join Filter: ("outer".intervalid = "inner".intervalid)
-> Nested Loop (cost=1.60..1489170.46 rows=72231 width=65)
Join Filter: ("outer".ccyindex = "inner".ccyindex)
-> Index Scan Backward using index_fxhistorical_ival_start on fxhistorical (cost=0.00..1411159.38 rows=3467088 width=56)
-> Materialize (cost=1.60..1.61 rows=1 width=13)
-> Seq Scan on fxccypairmap (cost=0.00..1.60 rows=1 width=13)
Filter: (ccypair = 'EUR/USD'::bpchar)
-> Materialize (cost=1.09..1.10 rows=1 width=10)
-> Seq Scan on fxinterval (cost=0.00..1.09 rows=1 width=10)
Filter: ((name)::text = '5MIN'::text)
EXPLAIN ANALYZE:
Limit (cost=2.69..72240.65 rows=500 width=71) (actual time=10.584..19744.503 rows=500 loops=1)
-> Nested Loop (cost=2.69..1490849.75 rows=10319 width=71) (actual time=10.570..19738.502 rows=500 loops=1)
Join Filter: ("outer".intervalid = "inner".intervalid)
-> Nested Loop (cost=1.60..1489223.46 rows=72231 width=65) (actual time=6.004..19707.766 rows=774 loops=1)
Join Filter: ("outer".ccyindex = "inner".ccyindex)
-> Index Scan Backward using index_fxhistorical_ival_start on fxhistorical (cost=0.00..1411212.38 rows=3467088 width=56) (actual time=0.115..18587.625 rows=37139 loops=1)
-> Materialize (cost=1.60..1.61 rows=1 width=13) (actual time=0.006..0.012 rows=1 loops=37139)
-> Seq Scan on fxccypairmap (cost=0.00..1.60 rows=1 width=13) (actual time=4.928..4.945 rows=1 loops=1)
Filter: (ccypair = 'EUR/USD'::bpchar)
-> Materialize (cost=1.09..1.10 rows=1 width=10) (actual time=0.012..0.018 rows=1 loops=774)
-> Seq Scan on fxinterval (cost=0.00..1.09 rows=1 width=10) (actual time=4.508..4.517 rows=1 loops=1)
Filter: ((name)::text = '1MIN'::text)
------------------------------------------
Sort (cost=81936.64..81962.67 rows=10412 width=71) (actual time=40029.203..40303.109 rows=47016 loops=1)
Sort Key: fxhistorical.ival_start
-> Hash Join (cost=418.19..81241.85 rows=10412 width=71) (actual time=285.880..39308.915 rows=47016 loops=1)
Hash Cond: ("outer".intervalid = "inner".intervalid)
-> Nested Loop (cost=417.10..80772.22 rows=72885 width=65) (actual time=285.729..38535.656 rows=72911 loops=1)
-> Seq Scan on fxccypairmap (cost=0.00..1.60 rows=1 width=13) (actual time=0.046..0.067 rows=1 loops=1)
Filter: (ccypair = 'EUR/USD'::bpchar)
-> Bitmap Heap Scan on fxhistorical (cost=417.10..79859.55 rows=72885 width=56) (actual time=285.658..37627.394 rows=72911 loops=1)
Recheck Cond: (fxhistorical.ccyindex = "outer".ccyindex)
-> Bitmap Index Scan on fxhistorical_ccyindexintervalid_index (cost=0.00..417.10 rows=72885 width=0) (actual time=272.450..272.450 rows=73111 loops=1)
Index Cond: (fxhistorical.ccyindex = "outer".ccyindex)
-> Hash (cost=1.09..1.09 rows=1 width=10) (actual time=0.077..0.077 rows=1 loops=1)
-> Seq Scan on fxinterval (cost=0.00..1.09 rows=1 width=10) (actual time=0.045..0.054 rows=1 loops=1)
Filter: ((name)::text = '1MIN'::text)
--------------------------------------------------------------------------
AFTER ANDREWSN QUERY:
Sort (cost=37816.33..37842.37 rows=10418 width=60) (actual time=38691.912..38965.303 rows=47036 loops=1)
Sort Key: fxhistorical.ival_start
InitPlan
-> Seq Scan on fxccypairmap (cost=0.00..1.60 rows=1 width=2) (actual time=13.615..13.631 rows=1 loops=1)
Filter: (ccypair = 'EUR/USD'::bpchar)
-> Nested Loop (cost=121.51..37119.49 rows=10418 width=60) (actual time=356.839..37996.363 rows=47036 loops=1)
-> Seq Scan on fxinterval (cost=0.00..1.09 rows=1 width=10) (actual time=3.216..3.231 rows=1 loops=1)
Filter: ((name)::text = '1MIN'::text)
-> Bitmap Heap Scan on fxhistorical (cost=121.51..36936.09 rows=14585 width=54) (actual time=339.949..37361.697 rows=47036 loops=1)
Recheck Cond: ((fxhistorical.ccyindex = $0) AND (fxhistorical.intervalid = "outer".intervalid))
-> Bitmap Index Scan on fxhistorical_ccyindexintervalid_index (cost=0.00..121.51 rows=14585 width=0) (actual time=326.595..326.595 rows=47139 loops=1)
Index Cond: ((fxhistorical.ccyindex = $0) AND (fxhistorical.intervalid = "outer".intervalid))
Total runtime: 39237.066 ms
-------------------------------------------------------------------
think this is it:
Limit (cost=2.69..13958.60 rows=500 width=60) (actual time=7.236..145.240 rows=500 loops=1)
InitPlan
-> Seq Scan on fxccypairmap (cost=0.00..1.60 rows=1 width=2) (actual time=4.002..4.018 rows=1 loops=1)
Filter: (ccypair = 'EUR/USD'::bpchar)
-> Nested Loop (cost=1.09..290786.46 rows=10418 width=60) (actual time=7.223..139.170 rows=500 loops=1)
Join Filter: ("outer".intervalid = "inner".intervalid)
-> Index Scan Backward using fxhistorical_ccyindexivalstart_index on fxhistorical (cost=0.00..289144.60 rows=72923 width=54) (actual time=4.137..109.935 rows=775 loops=1)
Index Cond: (ccyindex = $0)
-> Materialize (cost=1.09..1.10 rows=1 width=10) (actual time=0.010..0.016 rows=1 loops=775)
-> Seq Scan on fxinterval (cost=0.00..1.09 rows=1 width=10) (actual time=2.971..2.984 rows=1 loops=1)
Filter: ((name)::text = '1MIN'::text)
Total runtime: 148.292 ms
---------------------------------------------------
worse than the previous:
Limit (cost=2.69..2060.74 rows=500 width=52) (actual time=3869.692..3869.692 rows=0 loops=1)
InitPlan
-> Seq Scan on fxccypairmap (cost=0.00..1.60 rows=1 width=2) (actual time=2.052..2.067 rows=1 loops=1)
Filter: (ccypair = 'EUR/USD'::bpchar)
-> Seq Scan on fxinterval (cost=0.00..1.09 rows=1 width=2) (actual time=2.672..2.681 rows=1 loops=1)
Filter: ((name)::text = '1MIN'::text)
-> Index Scan Backward using pkey_fxhistorical on fxhistorical (cost=0.00..60033.38 rows=14585 width=52) (actual time=3869.677..3869.677 rows=0 loops=1)
Index Cond: ((ccyindex = $1) AND (intervalid = $0))
Total runtime: 3869.807 ms