performance issue with a specific query

Hi!

I hope I'm sending my question to the right list, please don't flame if it's
the wrong one.

I have noticed that while a query runs in about 1.5seconds on a 8.xx version
postgresql server on our 7.4.13 it takes around 15-20 minutes. Since we are
using RHEL4 on our server we are stuck with 7.4.13. The enormous time
difference between the different builds drives me crazy. Can you please help
me identifying the bottleneck or suggest anything to improve the dismal
performance.
The query is the following:

Select
car_license_plate.license_plate,
substr(date_trunc('day', car_km_fuel.transaction_time), 1, 10),
substr(date_trunc('second', car_km_fuel.transaction_time), 12, 8),
vehicle_make.make,
vehicle_type.model,
engine_size,
vehicle_fuel_type.fuel_type,
v_org_person_displayname.displayname_lastfirst,
car_km_fuel.ammount,
car_km_fuel.unit_price,
car_km_fuel.total_ammount,
currency.currency AS,
car_km_fuel.km AS,
vehicle_specific.fuel_capacity,
CASE WHEN (car_km_fuel.ammount > vehicle_specific.fuel_capacity) THEN
CAST(ROUND(CAST(car_km_fuel.ammount - vehicle_specific.fuel_capacity AS
NUMERIC), 2) AS varchar) ELSE '---' END AS "over",
car_km_fuel.notes,
CASE WHEN (prev_car_km_fuel.km IS NOT NULL AND car_km_fuel.km IS NOT NULL
AND (car_km_fuel.km - prev_car_km_fuel.km <> 0)) THEN
CAST(Round(CAST(((car_km_fuel.ammount / (car_km_fuel.km -
prev_car_km_fuel.km)) * 100) AS Numeric), 2) AS VARCHAR)
WHEN (prev_car_km_fuel.km IS NULL) THEN 'xxxx'
WHEN (car_km_fuel.km IS NULL) THEN 'error' END AS "average",
vehicle_specific.consumption_town,
org_person.email_address

FROM
car_km_fuel

LEFT JOIN
car ON car.id = car_km_fuel.car_id

LEFT JOIN
car_license_plate ON car_license_plate.car_id = car.id AND
(car_license_plate.license_plate_end_date < date_trunc('day',
car_km_fuel.transaction_time) OR car_license_plate.license_plate_end_date IS
NULL)
LEFT JOIN
vehicle_specific ON vehicle_specific.id = car.vehicle_specific_id

LEFT JOIN
vehicle_variant ON vehicle_variant.id =
vehicle_specific.vehicle_variant_id

LEFT JOIN
vehicle_type ON vehicle_type.id = vehicle_variant.vehicle_type_id

LEFT JOIN
vehicle_make ON vehicle_make.id = vehicle_type.vehicle_make_id

LEFT JOIN
vehicle_fuel_type ON vehicle_fuel_type.id = vehicle_specific.fuel_type_id

LEFT JOIN
car_driver ON car_driver.car_id = car.id AND
car_driver.allocation_date <= date_trunc('day',
car_km_fuel.transaction_time) AND
(car_driver.end_date >= date_trunc('day',
car_km_fuel.transaction_time) OR car_driver.end_date IS NULL)

LEFT JOIN
v_org_person_displayname ON v_org_person_displayname.id =
car_driver.car_driver_id

LEFT JOIN
org_person ON org_person.id = v_org_person_displayname.id

LEFT JOIN
currency ON currency.id = car_km_fuel.currency_id

LEFT JOIN
car_km_fuel AS prev_car_km_fuel ON
prev_car_km_fuel.transaction_time = (SELECT MAX(transaction_time) FROM
car_km_fuel as car_km_fuel2 WHERE car_km_fuel2.car_id = car.id AND
car_km_fuel2.transaction_time < car_km_fuel.transaction_time)

LEFT JOIN
org_company ON org_company.id = org_person.company_id

WHERE
(lower(org_company.name) LIKE lower(:param3) || '%') AND
(car_km_fuel.transaction_time >= :param1 OR :param1 IS NULL) AND
(car_km_fuel.transaction_time <= :param2 OR :param2 IS NULL)

ORDER BY
1, 2, 3;

The output of explain if the following under 7.4.13:

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=66.66..66.66 rows=1 width=917)
Sort Key: car_license_plate.license_plate,
substr((date_trunc('day'::text, car_km_fuel.transaction_time))::text,
1, 10), substr((date_trunc('second'::text,
car_km_fuel.transaction_time))::text, 12, 8)
-> Nested Loop (cost=44.93..66.65 rows=1 width=917)
-> Nested Loop Left Join (cost=44.93..62.23 rows=1 width=921)
Join Filter: ("inner".transaction_time = (subplan))
-> Nested Loop Left Join (cost=44.93..62.21 rows=1 width=917)
Join Filter: ("inner".id = "outer".currency_id)
-> Nested Loop (cost=44.93..60.92 rows=1 width=828)
-> Hash Join (cost=44.93..58.32 rows=1 width=805)
Hash Cond: ("outer".id =
"inner".car_driver_id)
-> Subquery Scan
v_org_person_displayname (cost=16.42..28.82 rows=196 width=520)
-> Merge Right Join
(cost=16.42..26.86 rows=196 width=51)
Merge Cond: ("outer".id
= "inner".company_id)
-> Index Scan using
pk_org_company on org_company co (cost=0.00..29.82 rows=47 width=27)
-> Sort
(cost=16.42..16.91 rows=196 width=28)
Sort Key: pers.company_id
-> Seq Scan on
org_person pers (cost=0.00..8.96 rows=196 width=28)
-> Hash (cost=28.51..28.51 rows=1 width=285)
-> Hash Join
(cost=19.81..28.51 rows=1 width=285)
Hash Cond:
("outer".car_id = "inner".car_id)
Join Filter:
((("outer".allocation_date)::timestamp without time zone <=
date_trunc('day'::text, "inner".transaction_time)) AND
((("outer".end_date)::timestamp without time zone >=
date_trunc('day'::text, "inner".transaction_time)) OR
("outer".end_date IS NULL)))
-> Seq Scan on
car_driver (cost=0.00..7.73 rows=173 width=16)
-> Hash
(cost=19.80..19.80 rows=4 width=285)
-> Hash Left Join
(cost=19.53..19.80 rows=4 width=285)
Hash Cond:
("outer".fuel_type_id = "inner".id)
-> Hash Left
Join (cost=18.50..18.72 rows=4 width=279)
Hash
Cond: ("outer".vehicle_make_id = "inner".id)
->
Merge Left Join (cost=17.38..17.53 rows=3 width=274)

Merge Cond: ("outer".vehicle_type_id = "inner".id)

-> Sort (cost=15.67..15.67 rows=2 width=265)

Sort Key: vehicle_variant.vehicle_type_id

-> Nested Loop Left Join (cost=0.00..15.66 rows=2 width=265)

Join Filter: ("inner".id = "outer".vehicle_variant_id)

-> Nested Loop Left Join (cost=0.00..13.83 rows=1
width=265)

Join Filter: ("inner".id =
"outer".vehicle_specific_id)

-> Nested Loop Left Join (cost=0.00..10.50 rows=1
width=234)

Join Filter:
((("inner".license_plate_end_date)::timestamp without time zone <
date_trunc('day'::text, "outer".transaction_time)) OR
("inner".license_plate_end_date IS NULL))

-> Nested Loop (cost=0.00..4.83 rows=1
width=224)

-> Seq Scan on car_km_fuel
(cost=0.00..0.00 rows=1 width=216)

Filter: (((transaction_time >=
'2005-01-01 00:00:00'::timestamp without time zone) OR (now() IS
NULL)) AND (((transaction_time)::timestamp with time zone <= now()) OR
(now() IS NULL)))

-> Index Scan using pk_car on car
(cost=0.00..4.82 rows=1 width=8)

Index Cond: (car.id =
"outer".car_id)

-> Index Scan using
ix_car_license_plate__car_id on car_license_plate (cost=0.00..5.65
rows=1 width=18)

Index Cond: (car_license_plate.car_id =
"outer".id)

-> Seq Scan on vehicle_specific (cost=0.00..2.59
rows=59 width=39)

-> Seq Scan on vehicle_variant (cost=0.00..1.37 rows=37
width=8)

-> Sort (cost=1.71..1.77 rows=22 width=17)

Sort Key: vehicle_type.id

-> Seq Scan on vehicle_type (cost=0.00..1.22 rows=22 width=17)
->
Hash (cost=1.10..1.10 rows=10 width=13)

-> Seq Scan on vehicle_make (cost=0.00..1.10 rows=10 width=13)
-> Hash
(cost=1.02..1.02 rows=2 width=14)
-> Seq
Scan on vehicle_fuel_type (cost=0.00..1.02 rows=2 width=14)
-> Index Scan using pk_org_person on
org_person (cost=0.00..2.59 rows=1 width=35)
Index Cond: (org_person.id =
"outer".car_driver_id)
-> Seq Scan on currency (cost=0.00..1.13
rows=13 width=97)
-> Seq Scan on car_km_fuel prev_car_km_fuel
(cost=0.00..0.00 rows=1 width=16)
SubPlan
-> Aggregate (cost=0.01..0.01 rows=1 width=8)
-> Seq Scan on car_km_fuel car_km_fuel2
(cost=0.00..0.00 rows=1 width=8)
Filter: ((car_id = $0) AND (transaction_time < $1))
-> Index Scan using pk_org_company on org_company
(cost=0.00..4.36 rows=1 width=4)
Index Cond: (org_company.id = "outer".company_id)
Filter: (lower((name)::text) ~~ '%'::text)

(64 rows)

If I leave off the where clause or run it on just a couple of recods, the
result is fine. Any ideas?

Regards
eliott