X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 80560D7F1B for ; Sat, 27 Aug 2005 19:18:05 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 63588-03 for ; Sat, 27 Aug 2005 22:18:03 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 206EED7F0A for ; Sat, 27 Aug 2005 19:18:01 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j7RMHpaX010923; Sat, 27 Aug 2005 18:17:52 -0400 (EDT) To: Mark Kirkwood Cc: Tobias Brox , pgsql-performance@postgresql.org Subject: Re: Limit + group + join In-reply-to: <430FD61A.3020902@paradise.net.nz> References: <20050826002709.GK10328@tobias.lan> <430E85ED.60109@paradise.net.nz> <9501.1125071193@sss.pgh.pa.us> <430FD61A.3020902@paradise.net.nz> Comments: In-reply-to Mark Kirkwood message dated "Sat, 27 Aug 2005 14:55:22 +1200" Date: Sat, 27 Aug 2005 18:17:51 -0400 Message-ID: <10922.1125181071@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200508/482 X-Sequence-Number: 14229 Mark Kirkwood writes: > 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; > [ fails to pick an available index-scan-backward plan ] 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. regards, tom lane