On May 20, 2009, at 7:17 PM, James B. Byrne wrote:
Looking at this I have to wonder what will be the effect of having tens of thousands of rate-pairs on file. Would this query be improved by first doing a sub-query on base/quote pairs that returned DISTINCT pairs and then do the IN condition using that?
If it turns out to be a problem a way around is to keep a reference to the 'actual' conversion rates from another table. Which ones are 'actual' would be updated by an INSERT trigger on your rates table. The amount of data in the new table (and subsequently the index on it's PK) would be far smaller and therefore likely a lot faster to query.
I've done something similar in a database where a history of states about records was kept around. Determining the 'actual' state was relatively slow because it was difficult to determine a method to uniquely point to it (the same issue with determining the latest timestamp of a group of records for the same data). Adding an FK from the record to it's 'actual' status record improved things a lot and had the added benefit that other derived information (detailed user information) was still easy to obtain with a simple join.
But as people often say here, premature optimisation is a waste of time, so don't go that route unless you have a reason to expect problems in that area.
Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a15269c10092027810544! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
