Skip site navigation (1) Skip section navigation (2)

Re: Planner doesn't look at LIMIT?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Dawid Kuroczko <qnex42(at)gmail(dot)com>
Cc: Postgresql Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Planner doesn't look at LIMIT?
Date: 2005-07-22 14:39:57
Message-ID: 29473.1122043197@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
Dawid Kuroczko <qnex42(at)gmail(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Madison KellyDate: 2005-07-22 14:46:42
Subject: Another index question
Previous:From: PFCDate: 2005-07-22 13:16:57
Subject: Re: Planner doesn't look at LIMIT?

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2005-07-22 14:40:45
Subject: Re: regressin failure on latest CVS
Previous:From: Andrew - SupernewsDate: 2005-07-22 14:36:20
Subject: Re: Timezone bugs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group