Обсуждение: Planning problem: pushing conditions through GROUP BY

Hoi,

I've having a smaller planner problem (server version 8.3.5). I have a
table with revisions (revs) and I have a view on top of that gives the
latest revision of each rule. When I request a specific rule, it works
fine.

# explain select * from maxrevs where rul_id=(select rul_id from rules where rul_sid=15895);
                                      QUERY PLAN
-----------------------------------------------------------------------------------------
 GroupAggregate  (cost=2.93..159.77 rows=2 width=8)
  InitPlan
    ->  Index Scan using rules_index_rul_sid on rules  (cost=0.00..4.27 rows=1 width=4)
          Index Cond: (rul_sid = 15895)
  ->  Bitmap Heap Scan on revs  (cost=2.93..159.32 rows=86 width=8)
        Recheck Cond: (rul_id = $0)
        ->  Bitmap Index Scan on revs_rul_id  (cost=0.00..2.91 rows=86 width=0)
              Index Cond: (rul_id = $0)
(8 rows)

But when I want to join on this view, it all goes pear shaped, as can
be seen in the following (completely equivalent) query:

# explain select * from maxrevs where rul_id=ANY(select rul_id from rules where rul_sid=15895);
                                        QUERY PLAN
---------------------------------------------------------------------------------------------
 Hash IN Join  (cost=6996.32..7080.72 rows=1 width=8)
  Hash Cond: (revs.rul_id = rules.rul_id)
  ->  HashAggregate  (cost=6992.04..7032.23 rows=3215 width=8)
        ->  Seq Scan on revs  (cost=0.00..5610.36 rows=276336 width=8)
  ->  Hash  (cost=4.27..4.27 rows=1 width=4)
        ->  Index Scan using rules_index_rul_sid on rules  (cost=0.00..4.27 rows=1 width=4)
              Index Cond: (rul_sid = 15895)
(7 rows)

As you can see, it correctly realises that there is only one rule that
matches, but fails to push this information through to the hash
aggregate. I would have expected a Nested Loop with a Bitmap Scan under
a GroupAggreate, as in the first case.

Switching between DISTINCT ON() and GROUP BY doesn't help, apparently
the planner is smart enough to see they are the same in this case.

If I manually rewrite the query to put a distinct at the outer level,
it all works fine. But the real situation is somewhat more complicated.
Is the kind of thing I can expect 8.4 to handle better? Or is there
some trick I can use to get the result I want?

Thanks in advance,

-- table definitions --

        Table "test.revs"
  Column    |  Type   | Modifiers
-------------+---------+-----------
 rev_id      | integer |
 rul_id      | integer |
 rev_content | text    |
Indexes:
   "revs_rev_id" btree (rev_id)
   "revs_rul_id" btree (rul_id)

    View "test.maxrevs"
 Column |  Type   | Modifiers
--------+---------+-----------
 rul_id | integer |
 rev_id | integer |
View definition:
 SELECT revs.rul_id, max(revs.rev_id) AS rev_id
  FROM revs
 GROUP BY revs.rul_id;

         Table "public.rules"
     Column       |  Type   | Modifiers
-------------------+---------+-----------
 rul_id            | integer | not null
 rul_sid           | integer | not null
 rul_repository_id | integer | not null
Indexes:
   "rules_rul_id_key" UNIQUE, btree (rul_id)
   "rules_index_rul_sid" btree (rul_sid)

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Martijn van Oosterhout <kleptog@svana.org> writes:
> I've having a smaller planner problem (server version 8.3.5). I have a
> table with revisions (revs) and I have a view on top of that gives the
> latest revision of each rule. When I request a specific rule, it works
> fine.
> ...
> But when I want to join on this view, it all goes pear shaped, as can
> be seen in the following (completely equivalent) query:

> # explain select * from maxrevs where rul_id=ANY(select rul_id from rules where rul_sid=15895);

There isn't any provision in the planner for trying to exchange the
order of a join and a GROUP BY operation.  In the general case it
wouldn't even be allowed anyway since it'd change the results.
In this specific case --- semijoin using an equality condition on the
grouping key --- I agree that it would be a legitimate transformation.
But it's not obvious that it would be a win.  I think if we wanted to
consider it we'd have to develop plans both ways and compare costs.
And that would be a really major amount of work.

The simpler case isn't treated as a join, just a simple WHERE condition
on individual rows, so it does what you want.

I think you'll have to write out your queries without benefit of the
view :-(.  Sorry the news isn't better.

            regards, tom lane

On Sun, Nov 22, 2009 at 12:38:36PM -0500, Tom Lane wrote:
> > But when I want to join on this view, it all goes pear shaped, as can
> > be seen in the following (completely equivalent) query:
>
> > # explain select * from maxrevs where rul_id=ANY(select rul_id from rules where rul_sid=15895);
>
> There isn't any provision in the planner for trying to exchange the
> order of a join and a GROUP BY operation.  In the general case it
> wouldn't even be allowed anyway since it'd change the results.
> In this specific case --- semijoin using an equality condition on the
> grouping key --- I agree that it would be a legitimate transformation.
> But it's not obvious that it would be a win.  I think if we wanted to
> consider it we'd have to develop plans both ways and compare costs.
> And that would be a really major amount of work.

It's not obvious it would be a win, but it would be a nice
transformation to have. In the actual case it's an inner join, but
since we know the other side only produces unique entries, the result
is the same.

> I think you'll have to write out your queries without benefit of the
> view :-(.  Sorry the news isn't better.

It's not the end of the world. At least I know it's it's not something
I'm missing. (I wonder if window functions work differently here.
That's 8.4 territory though).

Thanks anyway,

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.