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

Re: Bad plan for nested loop + limit

From: Alexander Staubo <alex(at)bengler(dot)no>
To: David Wilson <david(dot)t(dot)wilson(at)gmail(dot)com>
Cc: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Bad plan for nested loop + limit
Date: 2009-02-15 16:45:42
Message-ID: 88daf38c0902150845y2878fcbfmcd06d426b53a886a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sun, Feb 15, 2009 at 5:29 AM, David Wilson <david(dot)t(dot)wilson(at)gmail(dot)com> wrote:
> On Sat, Feb 14, 2009 at 5:25 PM, Alexander Staubo <alex(at)bengler(dot)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.

In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2009-02-15 17:35:04
Subject: Re: I/O increase after upgrading to 8.3.5
Previous:From: David WilsonDate: 2009-02-15 04:29:52
Subject: Re: Bad plan for nested loop + limit

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