Re: Planner doesn't look at LIMIT?

From: Tom Lane
Subject: Re: Planner doesn't look at LIMIT?
Date: ,
Msg-id: 16040.1123714524@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Planner doesn't look at LIMIT?  (Ian Westmacott)
Responses: Re: Planner doesn't look at LIMIT?  (Ian Westmacott)
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, )

Ian Westmacott <> writes:
> In a nutshell, I have a LIMIT query where the planner
> seems to favor a merge join over a nested loop.

The planner is already estimating only one row out of the join, and so
the LIMIT doesn't affect its cost estimates at all.

It appears to me that the reason the nestloop plan is fast is just
chance: a suitable matching row is found very early in the scan of
tableB, so that the indexscan on it can stop after 29 rows, instead
of having to go through all 55000 rows in the given range of bim.
If it'd have had to go through, say, half of the rows to find a match,
the sort/merge plan would show up a lot better.

If this wasn't chance, but was expected because there are many matching
rows and not only one, then there's a statistical problem.

            regards, tom lane


pgsql-performance by date:

From: Mark Cotner
Date:
Subject: Re: Speedier count(*)
From: "Qingqing Zhou"
Date:
Subject: Re: it is always delete temp table will slow down the postmaster?