Miscellany

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)