From: Alexander Staubo <alex@bengler.no>
To: David Wilson <david.t.wilson@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Bad plan for nested loop + limit
Date: Sun, 15 Feb 2009 17:45:42 +0100
Message-ID: <88daf38c0902150845y2878fcbfmcd06d426b53a886a@mail.gmail.com> (raw)
In-Reply-To: <e7f9235d0902142029h5b53baf2gede92b22afbbad35@mail.gmail.com>
References: <88daf38c0902141425s6972d849i9dad904f55d93c77@mail.gmail.com>
<e7f9235d0902142029h5b53baf2gede92b22afbbad35@mail.gmail.com>
On Sun, Feb 15, 2009 at 5:29 AM, David Wilson <david.t.wilson@gmail.com> wrote:
> On Sat, Feb 14, 2009 at 5:25 PM, Alexander Staubo <alex@bengler.no> wrote:
>>
>> Output from "explain analyze":
>>
>> Limit (cost=0.00..973.63 rows=4 width=48) (actual
>> time=61.554..4039.704 rows=1 loops=1)
>> -> Nested Loop (cost=0.00..70101.65 rows=288 width=48) (actual
>> time=61.552..4039.700 rows=1 loops=1)
>> -> Nested Loop (cost=0.00..68247.77 rows=297 width=52)
>> (actual time=61.535..4039.682 rows=1 loops=1)
>
> Those estimates are pretty far off. Did you try increasing the
> statistics target? Also, is the first query repeatable (that is, is it
> already in cache when you do the test, or alternately, are all queries
> *out* of cache when you test?)
All in the cache when I do the test. Ok, so upping the statistics to
100 on section_items.subject_id fixed it:
Limit (cost=3530.95..3530.96 rows=4 width=48) (actual
time=0.107..0.107 rows=1 loops=1)
-> Sort (cost=3530.95..3531.12 rows=66 width=48) (actual
time=0.106..0.106 rows=1 loops=1)
Sort Key: event_occurrences.start_time
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..3529.96 rows=66 width=48)
(actual time=0.098..0.100 rows=1 loops=1)
-> Index Scan using index_section_items_on_sandbox_id
on section_items (cost=0.00..104.29 rows=22 width=4) (actual
time=0.017..0.033 rows=7 loops=1)
Index Cond: (sandbox_id = 16399)
Filter: ((subject_type)::text = 'Event'::text)
-> Index Scan using
index_event_occurrences_on_event_id on event_occurrences
(cost=0.00..154.79 rows=74 width=48) (actual time=0.008..0.008 rows=0
loops=7)
Index Cond: (event_occurrences.event_id =
section_items.subject_id)
Filter: (event_occurrences.start_time >
'2009-02-14 18:15:14.739411+01'::timestamp with time zone)
Total runtime: 0.142 ms
Thanks.
Alexander.
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: alex@bengler.no, david.t.wilson@gmail.com
Subject: Re: Bad plan for nested loop + limit
In-Reply-To: <88daf38c0902150845y2878fcbfmcd06d426b53a886a@mail.gmail.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