Thread: Query performs badly with materialize node

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

Ingmar Brouns <swingi@gmail.com> writes:
> 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.

Yeah, that indexscan looks pretty odd to me too, but it's likely
explained by the context that you didn't provide.  The indexscan might
have been chosen because it provides output that's pre-sorted in the
order required by some higher-level part of the plan.  Or maybe you've
disabled seqscans, or done some other fooling with the planner cost
parameters.  There is certainly no way that a condition-free indexscan
will look cheaper than a seqscan with anything close to the default cost
settings.

In short: if you want to get useful commentary on your problem, you
need to provide a more complete picture.

            regards, tom lane

>
> In short: if you want to get useful commentary on your problem, you
> need to provide a more complete picture.
>
>                        regards, tom lane
>

I've posted the query plans now several times to this list, but they
do not show up, almost as if being caught by a spam filter or
something. I've wrote to pgsql-general-owner about this a few days ago
but haven't received a reply. Am I missing something?

Kind regards,

Ingmar

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.