Hi,
I have a query for which PostgreSQL 9.0.3 and 9.1 rc1 both come up with what
seems to be a very bad plan when materialize is enabled.
The plan with materialize takes 5 seconds to execute, the plan without 7 ms.
Part of the plan with materialization enabled
#effectively loop over all rows in the participates table
-> Nested Loop (cost=0.00..84762.57 rows=391
width=8) (actual time=0.863..5074.229 rows=45 loops=1)
Join Filter: (ptcp.cars_id = crs.id)
#This next index scan has no condition, so all rows:
-> Index Scan using ptcp_event_fk_i on
participates ptcp (cost=0.00..51591.41 rows=1105378 width=16) (actual
time=0.024..976.792 rows=1105028 loops=1)
-> Materialize (cost=0.00..9.82 rows=2
width=8) (actual time=0.000..0.001 rows=2 loops=1105028)
-> Index Scan using crs_owner on cars
crs (cost=0.00..9.81 rows=2 width=8) (actual time=0.021..0.024 rows=2
loops=1)
Index Cond: (owner = $3)
There is an index on the cars_id column of participates
There is an index on the owner column of cars
What I find strange is that there is a conditionless index scan on
participates, retrieving all its rows, and then a nested loop over all those
rows and a materialize node. Because there is an index on ptcp.cars_id, if
would expect the planner to use that index, instead of choosing to loop over
1105028 rows. When I disable materialize, this is exactly what it does
-> Index Scan using crs_owner on cars crs
(cost=0.00..9.81 rows=2 width=8) (actual time=0.076..0.079 rows=2 loops=1)
Index Cond: (owner = $3)
-> Index Scan using ptcp_car_fk_i on
participates ptcp (cost=0.00..779.41 rows=196 width=16) (actual
time=0.057..0.218 rows=22 loops=2)
Index Cond: (ptcp.cars_id = crs.id)
I know that postgresql's planner is driven by statistics, but this seems
strange...
Any thoughts?
Kind regards,
Ingmar
