From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Mark Kirkwood <markir@paradise.net.nz>
Cc: Tobias Brox <tobias@nordicbet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Limit + group + join
Date: Fri, 26 Aug 2005 07:18:30 -0700 (PDT)
Message-ID: <20050826071621.P12124@megazone.bigpanda.com> (raw)
In-Reply-To: <430E85ED.60109@paradise.net.nz>
References: <20050826002709.GK10328@tobias.lan>
<430E85ED.60109@paradise.net.nz>
On Fri, 26 Aug 2005, Mark Kirkwood wrote:
> However being a bit brutal:
>
> set enable_mergejoin=false;
> set enable_hashjoin=false;
>
> 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.24 rows=5 width=4)
> -> Group (cost=0.00..243798.00 rows=80000 width=4)
> -> Nested Loop (cost=0.00..243598.00 rows=80000 width=4)
> -> Index Scan Backward using c_pkey on c
> (cost=0.00..1518.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)
>
> What is interesting is why this plan is being rejected...
Well, it expects 80000 probles into b_on_c to be more expensive than the
hash join and sort. I wonder what explain analyze shows for the original
and the version with the enables changed.
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: sszabo@megazone.bigpanda.com, markir@paradise.net.nz, tobias@nordicbet.com
Subject: Re: Limit + group + join
In-Reply-To: <20050826071621.P12124@megazone.bigpanda.com>
* 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