SELECT 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