title: How to simplify nested SQL cross join?
body: <p>I'm using Postgres 9.3 and have the following four tables to have maximum flexibility regarding price and / or tax / taxe rate changes in the future (see below for more details):</p>
  
  <pre><code>CREATE TABLE main.products
  (
    id serial NOT NULL,
    "productName" character varying(255) NOT NULL,
    "productStockAmount" real NOT NULL,
  )
  
  CREATE TABLE main."productPrices"
  (
    id serial NOT NULL,
    product_id integer NOT NULL,
    "productPriceValue" real NOT NULL,
    "productPriceValidFrom" timestamp without time zone NOT NULL,
  )
  
  CREATE TABLE main."productTaxes"
  (
    id serial NOT NULL,
    product_id integer NOT NULL,
    "productTaxValidFrom" timestamp without time zone NOT NULL,
    "taxRate_id" integer NOT NULL,
  )
  
  CREATE TABLE main."taxRateValues"
  (
    id integer NOT NULL,
    "taxRate_id" integer NOT NULL,
    "taxRateValueValidFrom" timestamp without time zone NOT NULL,
    "taxRateValue" real,
  )
  </code></pre>
  
  <p>I built a view based on the following query to get the currently relevant values:</p>
  
  <pre><code>SELECT p.id, p."productName", p."productStockAmount", sub."productPriceValue", CHR(64+sub3."taxRate_id") AS taxRateId, sub3."taxRateValue" FROM main."products" p 
  CROSS JOIN LATERAL (SELECT * FROM main."productPrices" pp2 WHERE pp2."product_id"=p."id" AND pp2."productPriceValidFrom" &lt;= NOW() ORDER BY pp2."productPriceValidFrom" DESC LIMIT 1) AS sub 
  CROSS JOIN LATERAL (SELECT * FROM main."productTaxes" pt WHERE pt."product_id"=p."id" AND pt."productTaxValidFrom" &lt;= NOW() ORDER BY pt."productTaxValidFrom" DESC LIMIT 1) AS sub2 
  CROSS JOIN LATERAL (SELECT * FROM main."taxRateValues" trv WHERE trv."taxRate_id"=sub2."taxRate_id" AND trv."taxRateValueValidFrom" &lt;= NOW() ORDER BY trv."taxRateValueValidFrom" DESC LIMIT 1) AS sub3 
  </code></pre>
  
  <p>This works fine and gives me the correct results but I assume to get performance problems if several thousand products, price changes etc. are in the database. </p>
  
  <p>Is there anything I can do to simplify the statement or the overall database design?
  To use words to describe the needed flexibility:</p>
  
  <ul>
  <li>Prices can be changed and I have to record which price is valid to which time (archival, so not only the current price is needed)</li>
  <li>Applied tax rates for products can be changed (e.g. due to changes by law) - archival also needed</li>
  <li>Tax rates in general can be changed (also by law, but not related to a single product but all products with this identifier)</li>
  </ul>
  
  <p>Some examples of things that can happen:</p>
  
  <ul>
  <li>Product X changes price from 100 to 200 at 2014-05-09</li>
  <li>Product X changes tax rate from A to B at 2014-07-01</li>
  <li>Tax rate value for tax rate A changes from 16 to 19 at 2014-09-01</li>
  </ul>