Tom Lane wrote:
I looked into this and found that indeed the desirable join plan was
getting generated, but it wasn't picked because query_planner didn't
have an accurate idea of how much of the join needed to be scanned to
satisfy the GROUP BY step. I've committed some changes that hopefully
will let 8.1 be smarter about GROUP BY ... LIMIT queries.
Very nice :-)
joinlimit=# EXPLAIN SELECT c.id FROM c JOIN b ON c_id=c.id GROUP BY
c.id ORDER BY c.id DESC LIMIT 5;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Limit (cost=0.00..15.23 rows=5 width=4)
-> Group (cost=0.00..243730.00 rows=80000 width=4)
-> Nested Loop (cost=0.00..243530.00 rows=80000 width=4)
-> Index Scan Backward using c_pkey on c
(cost=0.00..1450.00 rows=80000 width=4)
-> Index Scan using b_on_c on b (cost=0.00..3.01
rows=1 width=4)
Index Cond: (b.c_id = "outer".id)
(6 rows)
This is 8.1devel from today.
regards
Mark
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
