Re: Query Optimizer makes a poor choice

>>On Tue, Nov 29, 2011 at 7:21 PM, Tyler Hains
<thains(at)profitpointinc(dot)com> wrote:
>> # explain analyze select * from cards where card_set_id=2850 order by
>> card_id limit 1;
>>                                                                QUERY
PLAN
>>
------------------------------------------------------------------------
------------------------------------------->----------------------
>>  Limit  (cost=0.00..105.19 rows=1 width=40) (actual
time=6026.947..6026.948
>> rows=1 loops=1)
>>    ->  Index Scan using cards_pkey on cards  (cost=0.00..2904875.38
>> rows=27616 width=40) (actual time=6026.945..6026.945 rows=1 loops=1)
>>          Filter: (card_set_id = 2850)
>>  Total runtime: 6026.985 ms
>> (4 rows)
>>
>
>I believe this is the old problem of the planner expecting that the
>card_set_id's are randomly distributed over the card_ids . This is not
>the case, I guess?
>
>The planner expects to quickly hit a matching record while scanning
>the primary key, an there is a nasty surprise.
>
>It seems there is no perfect solution, things You might want to try:
>-fooling with random_page_cost/seq_tuple_cost/work_mem
>-"order by card_id-1"
>-an index on (card_set_id, card_id)
>
>Greetings
>Marcin

Ahhh! This makes a lot of sense. Yes, a card set encompasses a specific
range of cards. With that, it might make a big difference to add an
index on the combination of the two fields...

Thanks!
Tyler