From: Mark Kirkwood <markir@paradise.net.nz>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Tobias Brox <tobias@nordicbet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Limit + group + join
Date: Sun, 28 Aug 2005 12:48:59 +1200
Message-ID: <431109FB.6000505@paradise.net.nz> (raw)
In-Reply-To: <10922.1125181071@sss.pgh.pa.us>
References: <20050826002709.GK10328@tobias.lan>
<430E85ED.60109@paradise.net.nz>
<9501.1125071193@sss.pgh.pa.us>
<430FD61A.3020902@paradise.net.nz>
<10922.1125181071@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: markir@paradise.net.nz, tgl@sss.pgh.pa.us, tobias@nordicbet.com
Subject: Re: Limit + group + join
In-Reply-To: <431109FB.6000505@paradise.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox