From: | Ingmar Brouns <swingi(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Query performs badly with materialize node |
Date: | 2011-09-19 13:49:52 |
Message-ID: | CA+77E=aotMskVkfBhsbStQT4-Hd0OzfyXu8U0GnAMXUkQnpArA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2011-09-19 14:17:17 | Re: General guidance if there is an in dadabase solution or should stay as excel vba solution. |
Previous Message | Siva Palanisamy | 2011-09-19 13:20:03 | How to pass array from .pgc to sql function |