Drupal and PostgreSQL acceleration.

My company has been building a web application on top of a Drupal 5.x core and a PostgreSQL 8.1.11 base. We have seen that running Views with PostgreSQL slows drupal down to a snail and thus makes the site less enjoyable. We have enabled the Devel module and have been clocking the queries that run on each page and would like to know if there are any tweaks that we can do to Views or PostgreSQL configurations that might get the load time under 4 seconds. Here is an excerpt of the top 20 heaviest queries from one of our pages.

200.25  0   sess_write  UPDATE users SET access = 1225912935 WHERE uid = 36
49.42   0   devel_switch_user_list  SELECT DISTINCT u.uid, u.name, u.access FROM users u LEFT JOIN users_roles r ON u.uid = r.uid WHERE u.uid = 1 OR r.rid IN (4) ORDER BY u.access DESC LIMIT 10 OFFSET 0
33.09   0   views_build_view    SELECT DISTINCT(node.nid), node_data_field_item_date.field_item_date_value AS node_data_field_item_date_field_item_date_value FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_data term_data ON term_node2.tid = term_data.tid LEFT JOIN content_type_item node_data_field_item_date ON node.vid = node_data_field_item_date.vid WHERE (node.status = '1') AND (node.type IN ('item')) AND (term_node.tid IS NULL) AND (term_data.name = 'Italian') GROUP BY node.nid, node_data_field_item_date_field_item_date_value ORDER BY node_data_field_item_date_field_item_date_value DESC LIMIT 3 OFFSET 0
28.55   0   drupal_lookup_path  SELECT COUNT(pid) FROM url_alias
20.1    0   cache_get   SELECT data, created, headers, expire FROM cache_views WHERE cid = 'views_tables:en'
15.39   0   views_build_view    SELECT DISTINCT(node.nid), node_data_field_item_date.field_item_date_value AS node_data_field_item_date_field_item_date_value FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_data term_data2 ON term_node2.tid = term_data2.tid LEFT JOIN term_node term_node3 ON node.nid = term_node3.nid LEFT JOIN term_data term_data3 ON term_node3.tid = term_data3.tid LEFT JOIN content_type_item node_data_field_item_date ON node.vid = node_data_field_item_date.vid WHERE (node.status = '1') AND (node.type IN ('item')) AND (term_data.name = 'Boston') AND (term_data2.name = 'Style') AND (term_data3.name = 'Shops') GROUP BY node.nid, node_data_field_item_date_field_item_date_value ORDER BY node_data_field_item_date_field_item_date_value DESC LIMIT 3 OFFSET 0
13  0   thrillist_find_todays_ads   SELECT DISTINCT node.nid AS ad_slot_nid, node.title AS ad_slot_tag, d1.tid AS edition_tid, d3.name AS ad_size, ctas.field_ad_slot_html_value as slot_html FROM term_node t1, term_node t3, term_data d1, term_data d3, node, content_type_ad_slot ctas, content_type_ad cta, content_field_ad_is_email_ad isemail WHERE d1.tid= t1.tid AND d3.tid = t3.tid AND d1.name='Boston' AND d1.vid=12 AND d3.vid=7 AND node.nid = t1.nid AND node.nid = t3.nid AND node.type='ad_slot' AND ctas.nid = node.nid AND ctas.nid = cta.field_ad_ad_slot_nid AND cta.field_ad_flight_dates_value < 1225912934 AND cta.field_ad_flight_dates_value2 > 1225912934 AND cta.field_ad_is_web_ad_value = 1;
11.04   0   views_build_view    SELECT DISTINCT(node.nid), node_data_field_item_date.field_item_date_value AS node_data_field_item_date_field_item_date_value FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_data term_data2 ON term_node2.tid = term_data2.tid LEFT JOIN term_node term_node3 ON node.nid = term_node3.nid LEFT JOIN term_data term_data3 ON term_node3.tid = term_data3.tid LEFT JOIN content_type_item node_data_field_item_date ON node.vid = node_data_field_item_date.vid WHERE (node.status = '1') AND (node.type IN ('item')) AND (term_data.name = 'Boston') AND (term_data2.name = 'Food Dining') AND (term_data3.name = 'Italian') GROUP BY node.nid, node_data_field_item_date_field_item_date_value ORDER BY node_data_field_item_date_field_item_date_value DESC LIMIT 3 OFFSET 0
9.4 0   views_build_view    SELECT node.nid, node_data_field_item_date.field_item_date_value AS node_data_field_item_date_field_item_date_value FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN content_type_item node_data_field_item_date ON node.vid = node_data_field_item_date.vid WHERE (node.status = '1') AND (node.type IN ('item')) AND (term_data.name = 'Boston') ORDER BY node_data_field_item_date_field_item_date_value DESC LIMIT 1 OFFSET 0
8.58    0   cache_get   SELECT data, created, headers, expire FROM cache_menu WHERE cid = '36:en'
6.42    0   sess_write  UPDATE sessions SET uid = 36, cache = 0, hostname = '209.131.121.170', session = 'edition|s:6:"Boston";login_layer|b:1;vertical|s:5:"Style";', timestamp = 1225912935 WHERE sid = 'c2482a4cc14c8374468b9a28d3e00dd2'
6.13    0   views_build_view    SELECT DISTINCT(node.nid), node_data_field_item_date.field_item_date_value AS node_data_field_item_date_field_item_date_value FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_data term_data2 ON term_node2.tid = term_data2.tid LEFT JOIN content_type_item node_data_field_item_date ON node.vid = node_data_field_item_date.vid WHERE (node.status = '1') AND (node.type IN ('item')) AND (term_data.name = 'Boston') AND (term_data2.name = 'Bars Nightlife') GROUP BY node.nid, node_data_field_item_date_field_item_date_value ORDER BY node_data_field_item_date_field_item_date_value DESC LIMIT 10 OFFSET 3
5.47    0   views_build_view    SELECT node.nid, node_data_field_item_date.field_item_date_value AS node_data_field_item_date_field_item_date_value FROM node node LEFT JOIN content_type_item node_data_field_item_date ON node.vid = node_data_field_item_date.vid LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_data term_data2 ON term_node2.tid = term_data2.tid WHERE (node.status = '1') AND (node.type IN ('item')) AND (node_data_field_item_date.field_item_date_value::ABSTIME >= '2007-12-06 00:00:00') AND (term_data.name = 'Boston') AND (term_data2.name = 'Home Gadgets') ORDER BY node_data_field_item_date_field_item_date_value DESC LIMIT 1 OFFSET 2
5.37    0   pager_query_with_offset SELECT DISTINCT(node.nid), node_data_field_item_date.field_item_date_value AS node_data_field_item_date_field_item_date_value FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN content_type_item node_data_field_item_date ON node.vid = node_data_field_item_date.vid WHERE (node.type IN ('item')) AND (node.status = '1') AND (term_data.name = 'Boston') GROUP BY node.nid, node_data_field_item_date_field_item_date_value ORDER BY node_data_field_item_date_field_item_date_value DESC LIMIT 5 OFFSET 1
5.19    0   pager_query_with_offset SELECT count(DISTINCT(node.nid)) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN content_type_item node_data_field_item_date ON node.vid = node_data_field_item_date.vid WHERE (node.type IN ('item')) AND (node.status = '1') AND (term_data.name = 'Boston')
4.65    0   views_build_view    SELECT node.nid, node_data_field_item_date.field_item_date_value AS node_data_field_item_date_field_item_date_value FROM node node LEFT JOIN content_type_item node_data_field_item_date ON node.vid = node_data_field_item_date.vid LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_data term_data2 ON term_node2.tid = term_data2.tid WHERE (node.status = '1') AND (node.type IN ('item')) AND (node_data_field_item_date.field_item_date_value::ABSTIME >= '2007-12-06 00:00:00') AND (term_data.name = 'Boston') AND (term_data2.name = 'Travel') ORDER BY node_data_field_item_date_field_item_date_value DESC LIMIT 1 OFFSET 3
4.65    0   views_build_view    SELECT node.nid, node_data_field_item_date.field_item_date_value AS node_data_field_item_date_field_item_date_value FROM node node LEFT JOIN content_type_item node_data_field_item_date ON node.vid = node_data_field_item_date.vid LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_data term_data2 ON term_node2.tid = term_data2.tid WHERE (node.status = '1') AND (node.type IN ('item')) AND (node_data_field_item_date.field_item_date_value::ABSTIME >= '2007-12-06 00:00:00') AND (term_data.name = 'Boston') AND (term_data2.name = 'Politics') ORDER BY node_data_field_item_date_field_item_date_value DESC LIMIT 1 OFFSET 0
4.45    0   views_build_view    SELECT DISTINCT(node.nid), node_data_field_item_date.field_item_date_value AS node_data_field_item_date_field_item_date_value FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_data term_data2 ON term_node2.tid = term_data2.tid LEFT JOIN content_type_item node_data_field_item_date ON node.vid = node_data_field_item_date.vid WHERE (node.status = '1') AND (node.type IN ('item')) AND (term_data.name = 'Boston') AND (term_data2.name = 'Sports Fitness') GROUP BY node.nid, node_data_field_item_date_field_item_date_value ORDER BY node_data_field_item_date_field_item_date_value DESC LIMIT 10 OFFSET 0
4.43    0   views_build_view    SELECT DISTINCT(node.nid), node_data_field_item_date.field_item_date_value AS node_data_field_item_date_field_item_date_value FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_data term_data2 ON term_node2.tid = term_data2.tid LEFT JOIN content_type_item node_data_field_item_date ON node.vid = node_data_field_item_date.vid WHERE (node.status = '1') AND (node.type IN ('item')) AND (term_data.name = 'Boston') AND (term_data2.name = 'Quizzes and Time Wasters') GROUP BY node.nid, node_data_field_item_date_field_item_date_value ORDER BY node_data_field_item_date_field_item_date_value DESC LIMIT 3 OFFSET 2
2.81    0   cache_get   SELECT data, created, headers, expire FROM cache_content WHERE cid = 'content_type_info'

Where would we even start with accelerating this application?