Re: Bad plan for nested loop + limit

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.