From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dawid Kuroczko <qnex42@gmail.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Planner doesn't look at LIMIT?
Date: Fri, 22 Jul 2005 10:39:57 -0400
Message-ID: <29473.1122043197@sss.pgh.pa.us> (raw)
In-Reply-To: <758d5e7f0507220210bfa4978@mail.gmail.com>
References: <758d5e7f0507220210bfa4978@mail.gmail.com>
Dawid Kuroczko <qnex42@gmail.com> writes:
> qnex=# EXPLAIN SELECT * FROM log NATURAL JOIN useragents LIMIT 1;
> Limit (cost=15912.20..15912.31 rows=1 width=272)
> -> Hash Join (cost=15912.20..5328368.96 rows=47044336 width=272)
> If I set enable_hashjoin=false:
> qnex=# EXPLAIN ANALYZE SELECT * FROM log NATURAL LEFT JOIN useragents LIMIT 1;
> Limit (cost=0.00..3.07 rows=1 width=272) (actual time=74.214..74.216
> rows=1 loops=1)
> -> Nested Loop Left Join (cost=0.00..144295895.01 rows=47044336
> width=272) (actual time=74.204..74.204 rows=1 loops=1)
This is quite strange. The nestloop plan definitely should be preferred
in the context of the LIMIT, considering that it has far lower estimated
cost. And it is preferred in simple tests for me. It seems there must
be something specific to your installation that's causing the planner to
go wrong. Can you develop a self-contained test case that behaves this
way for you?
I recall we saw a similar complaint a month or two back, but the
complainant never followed up with anything useful for tracking down
the problem.
regards, tom lane
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: tgl@sss.pgh.pa.us, qnex42@gmail.com
Subject: Re: Planner doesn't look at LIMIT?
In-Reply-To: <29473.1122043197@sss.pgh.pa.us>
* 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