Re: Planner doesn't look at LIMIT?

From: Tom Lane
Subject: Re: Planner doesn't look at LIMIT?
Date: ,
Msg-id: 29473.1122043197@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Planner doesn't look at LIMIT?  (Dawid Kuroczko)
Responses: Re: Planner doesn't look at LIMIT?  (Dawid Kuroczko)
Re: Planner doesn't look at LIMIT?  (Tom Lane)
List: pgsql-performance

Tree view

Planner doesn't look at LIMIT?  (Dawid Kuroczko, )
 Re: Planner doesn't look at LIMIT?  (PFC, )
 Re: Planner doesn't look at LIMIT?  (Tom Lane, )
  Re: Planner doesn't look at LIMIT?  (Dawid Kuroczko, )
  Re: Planner doesn't look at LIMIT?  (Tom Lane, )
   Re: [HACKERS] Planner doesn't look at LIMIT?  (Simon Riggs, )
    Re: [HACKERS] Planner doesn't look at LIMIT?  (Tom Lane, )
     Re: [HACKERS] Planner doesn't look at LIMIT?  (Sam Mason, )
   Re: Planner doesn't look at LIMIT?  (Dawid Kuroczko, )
   Re: Planner doesn't look at LIMIT?  (Ian Westmacott, )
    Re: Planner doesn't look at LIMIT?  (Tom Lane, )
     Re: Planner doesn't look at LIMIT?  (Ian Westmacott, )
 Re: Planner doesn't look at LIMIT?  (Sam Mason, )

Dawid Kuroczko <> 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


pgsql-performance by date:

From: Mark Wong
Date:
Subject: Re: [PATCHES] COPY FROM performance improvements
From: Dawid Kuroczko
Date:
Subject: Re: Planner doesn't look at LIMIT?