Query Optimisation

[ConradIrwin]

Hi, I am storing the text for certain sections in my site in a multilingual, multi-revisional table (site_text) And, in order to render a page I need to extract all the relevant information from there by referencing the keys from (site_page).

I have the following query, but I am worried about how fast it will run, if it follows the face-value SQL then it will be very slow. Does postgresql notice things like this and work out a better way of phrasing them? Is there a better way of writing this in SQL? or a better way of doing this for postgresql?

site_text has columns for "l(anguage)id" and "revision" number in addition to the primary lookup "st".
The columns in site_page each contain one value for "st".

SELECT t.string, k.string, d.string, c.string FROM site_page
  JOIN (SELECT site_text.st, site_text.string 
    FROM site_text 
      WHERE site_text.lid=1 AND site_text.revision=0) t 
    ON t.st = site_page.title
  JOIN (SELECT site_text.st, site_text.string 
    FROM site_text
      WHERE site_text.lid=1 AND site_text.revision=0) k 
    ON k.st = site_page.keywords
  JOIN (SELECT site_text.st, site_text.string
    FROM site_text 
      WHERE site_text.lid=1 AND site_text.revision=0) d 
    ON d.st = site_page.title
  JOIN (SELECT site_text.st, site_text.string 
    FROM site_text 
      WHERE site_text.lid=1 AND site_text.revision=0) c 
    ON c.st = site_page.keywords
  WHERE site_page.pid = 1;