Unnamed

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)