Re: Query Optimizer makes a poor choice

On Tue, Nov 29, 2011 at 11:21 AM, 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)

There's a huge disconnect here between what the query planner expects
(27k rows) and how many there are (1). Also, getting a single row
from an index should be faster than this, even if the table and index
are quite large. Have you checked for bloat on this index?