Planner problem

Hi!

I am having a bit of problem with the plan that the planner produces.

First a little info:

OS: Linux Debian
Version: PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc GCC)
3.3.3 (Debian 20040401)
CPU: AMD Athlon XP 2000+
Memory: 1GB
Disk: 4 SCSI-3 UW 10000rpm in RAID 1 (mirror) mode

shared_mem: 20000
Sort mem: 8192
effective_cache_size: 80000

Row count for the tables involved:
priceavailable: 8564
product: 3
productCodeAlias: 7
locationIATA: 3402
locationConnection: 64
price: 4402

I have runned both vacuum full and vacuum analyze before I run my query
and shared_mem is set to 20000

Here is the result I am getting:

EXPLAIN ANALYZE
SELECT
CASE WHEN 'D' = 'A' THEN price.priceArrival ELSE price.priceDeparture
END AS price,
price.vatPercent AS vatPercent
FROM
priceavailable pa,
product product,
productCodeAlias productAlias,
locationiata la,
locationconnection lc,
price price
WHERE
pa.direction IN('D', 'B')
AND pa.productId = product.productId
AND product.productId = productAlias.productId
AND productAlias.productCode = 'TAXI'
AND pa.locationConnectionId = lc.locationConnectionId
AND lc.locationConnectionCode = 'RNB'
AND pa.locationId = la.locationId
AND la.iataCode = 'KB8'
AND price.pricegroupId = pa.priceGroupId
AND price.productId = product.productId
AND '2004-06-01 05:30:00.000000+02' BETWEEN price.validstartdate AND
price.validstopdate
AND product.organizationId = 1
AND price.organizationId = product.organizationId
AND pa.deletionDate IS NULL
AND product.deletionDate IS NULL
AND la.deletionDate IS NULL
AND lc.deletionDate IS NULL
AND price.deletionDate IS NULL
;

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.14..17.16 rows=1 width=8) (actual
time=3274.196..4620.299 rows=1 loops=1)
-> Nested Loop (cost=2.14..13.49 rows=1 width=12) (actual
time=2643.441..4617.246 rows=47 loops=1)
Join Filter: (("outer".productid = "inner".productid) AND
("inner".pricegroupid = "outer".pricegroupid))
-> Nested Loop (cost=0.00..7.72 rows=1 width=12) (actual
time=0.238..5.455 rows=111 loops=1)
-> Seq Scan on locationconnection lc (cost=0.00..1.80
rows=1 width=4) (actual time=0.153..0.245 rows=1 loops=1)
Filter: ((locationconnectioncode = 'RNB'::text)
AND (deletiondate IS NULL))
-> Index Scan using priceavailableix1 on priceavailable
pa (cost=0.00..5.91 rows=1 width=16) (actual time=0.067..4.182 rows=111
loops=1)
Index Cond: (pa.locationconnectionid =
"outer".locationconnectionid)
Filter: (((direction = 'D'::text) OR (direction =
'B'::text)) AND (deletiondate IS NULL))
-> Hash Join (cost=2.14..5.74 rows=2 width=24) (actual
time=0.058..39.116 rows=1243 loops=111)
Hash Cond: ("outer".productid = "inner".productid)
-> Index Scan using priceix2 on price (cost=0.00..3.41
rows=23 width=20) (actual time=0.042..25.811 rows=4402 loops=111)
Index Cond: ('2004-06-01'::date <= validstopdate)
Filter: (('2004-06-01'::date >= validstartdate)
AND (deletiondate IS NULL) AND (organizationid = 1))
-> Hash (cost=2.14..2.14 rows=1 width=12) (actual
time=0.132..0.132 rows=0 loops=1)
-> Nested Loop (cost=0.00..2.14 rows=1 width=12)
(actual time=0.088..0.123 rows=1 loops=1)
Join Filter: ("outer".productid =
"inner".productid)
-> Seq Scan on product (cost=0.00..1.04
rows=1 width=8) (actual time=0.013..0.022 rows=3 loops=1)
Filter: ((organizationid = 1) AND
(deletiondate IS NULL))
-> Seq Scan on productcodealias
productalias (cost=0.00..1.09 rows=1 width=4) (actual time=0.022..0.024
rows=1 loops=3)
Filter: (productcode = 'TAXI'::text)
-> Index Scan using locationiataix4 on locationiata la
(cost=0.00..3.66 rows=1 width=4) (actual time=0.052..0.052 rows=0 loops=47)
Index Cond: ("outer".locationid = la.locationid)
Filter: ((iatacode = 'KB8'::text) AND (deletiondate IS NULL))
Total runtime: 4620.852 ms

If I do an "set enable_nestloop = 0" and run the exact same question I get:

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=150.55..154.20 rows=1 width=8) (actual
time=68.095..103.006 rows=1 loops=1)
Hash Cond: ("outer".locationid = "inner".locationid)
-> Hash Join (cost=145.65..149.29 rows=1 width=12) (actual
time=66.304..102.709 rows=47 loops=1)
Hash Cond: (("outer".productid = "inner".productid) AND
("outer".pricegroupid = "inner".pricegroupid))
-> Hash Join (cost=2.15..5.75 rows=2 width=24) (actual
time=0.343..38.844 rows=1243 loops=1)
Hash Cond: ("outer".productid = "inner".productid)
-> Index Scan using priceix2 on price (cost=0.00..3.41
rows=23 width=20) (actual time=0.053..26.225 rows=4402 loops=1)
Index Cond: ('2004-06-01'::date <= validstopdate)
Filter: (('2004-06-01'::date >= validstartdate)
AND (deletiondate IS NULL) AND (organizationid = 1))
-> Hash (cost=2.14..2.14 rows=1 width=12) (actual
time=0.191..0.191 rows=0 loops=1)
-> Hash Join (cost=1.09..2.14 rows=1 width=12)
(actual time=0.174..0.184 rows=1 loops=1)
Hash Cond: ("outer".productid =
"inner".productid)
-> Seq Scan on product (cost=0.00..1.04
rows=1 width=8) (actual time=0.017..0.025 rows=3 loops=1)
Filter: ((organizationid = 1) AND
(deletiondate IS NULL))
-> Hash (cost=1.09..1.09 rows=1 width=4)
(actual time=0.073..0.073 rows=0 loops=1)
-> Seq Scan on productcodealias
productalias (cost=0.00..1.09 rows=1 width=4) (actual time=0.042..0.045
rows=1 loops=1)
Filter: (productcode = 'TAXI'::text)
-> Hash (cost=143.50..143.50 rows=1 width=12) (actual
time=61.650..61.650 rows=0 loops=1)
-> Merge Join (cost=1.81..143.50 rows=1 width=12)
(actual time=59.914..61.419 rows=111 loops=1)
Merge Cond: ("outer".locationconnectionid =
"inner".locationconnectionid)
-> Index Scan using priceavailableix1 on
priceavailable pa (cost=0.00..141.57 rows=43 width=16) (actual
time=0.048..48.739 rows=6525 loops=1)
Filter: (((direction = 'D'::text) OR
(direction = 'B'::text)) AND (deletiondate IS NULL))
-> Sort (cost=1.81..1.81 rows=1 width=4) (actual
time=0.215..0.290 rows=1 loops=1)
Sort Key: lc.locationconnectionid
-> Seq Scan on locationconnection lc
(cost=0.00..1.80 rows=1 width=4) (actual time=0.125..0.194 rows=1 loops=1)
Filter: ((locationconnectioncode =
'RNB'::text) AND (deletiondate IS NULL))
-> Hash (cost=4.89..4.89 rows=1 width=4) (actual time=0.122..0.122
rows=0 loops=1)
-> Index Scan using locationiataix1 on locationiata la
(cost=0.00..4.89 rows=1 width=4) (actual time=0.108..0.112 rows=1 loops=1)
Index Cond: (iatacode = 'KB8'::text)
Filter: (deletiondate IS NULL)
Total runtime: 103.563 ms

Which is a lot faster than the original question.

If I then enable nestloop again and lower the geqo_threshold from 11
(default) to 5 and run the same query again I get:

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=7.72..17.19 rows=1 width=8) (actual
time=9.752..42.278 rows=1 loops=1)
-> Nested Loop (cost=7.72..13.52 rows=1 width=12) (actual
time=5.698..41.079 rows=47 loops=1)
Join Filter: ("outer".productid = "inner".productid)
-> Nested Loop (cost=7.72..12.47 rows=1 width=28) (actual
time=5.663..39.992 rows=47 loops=1)
Join Filter: ("inner".productid = "outer".productid)
-> Hash Join (cost=7.72..11.37 rows=1 width=24)
(actual time=5.470..36.848 rows=111 loops=1)
Hash Cond: (("outer".pricegroupid =
"inner".pricegroupid) AND ("outer".productid = "inner".productid))
-> Index Scan using priceix2 on price
(cost=0.00..3.41 rows=23 width=20) (actual time=0.050..26.475 rows=4402
loops=1)
Index Cond: ('2004-06-01'::date <=
validstopdate)
Filter: (('2004-06-01'::date >=
validstartdate) AND (deletiondate IS NULL) AND (organizationid = 1))
-> Hash (cost=7.72..7.72 rows=1 width=12)
(actual time=1.809..1.809 rows=0 loops=1)
-> Nested Loop (cost=0.00..7.72 rows=1
width=12) (actual time=0.253..1.587 rows=111 loops=1)
-> Seq Scan on locationconnection lc
(cost=0.00..1.80 rows=1 width=4) (actual time=0.163..0.234 rows=1 loops=1)
Filter: ((locationconnectioncode
= 'RNB'::text) AND (deletiondate IS NULL))
-> Index Scan using priceavailableix1
on priceavailable pa (cost=0.00..5.91 rows=1 width=16) (actual
time=0.070..0.965 rows=111 loops=1)
Index Cond:
(pa.locationconnectionid = "outer".locationconnectionid)
Filter: (((direction =
'D'::text) OR (direction = 'B'::text)) AND (deletiondate IS NULL))
-> Seq Scan on productcodealias productalias
(cost=0.00..1.09 rows=1 width=4) (actual time=0.020..0.022 rows=1 loops=111)
Filter: (productcode = 'TAXI'::text)
-> Seq Scan on product (cost=0.00..1.04 rows=1 width=8)
(actual time=0.005..0.013 rows=3 loops=47)
Filter: ((organizationid = 1) AND (deletiondate IS NULL))
-> Index Scan using locationiataix4 on locationiata la
(cost=0.00..3.66 rows=1 width=4) (actual time=0.022..0.022 rows=0 loops=47)
Index Cond: ("outer".locationid = la.locationid)
Filter: ((iatacode = 'KB8'::text) AND (deletiondate IS NULL))
Total runtime: 42.852 ms

Which is even faster than disable:ing nestloop.

So my question is why is the planner making such a bad choice and how
can I make it choose a better plan?

/Mikael