Re: Query Optimizer makes a poor choice

On 29.11.2011 21:34, Scott Marlowe wrote:
> 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?

No there isn't - the "1" is actually caused by the LIMIT clause. Once
the first row is returned, it does not fetch the following ones.

The bloat might be the cause, though. Tyler, run this and let us know
the results:

1) SELECT relpages, reltuples FROM pg_class WHERE relname = 'cards';

2) SELECT n_live_tup, n_dead_tup FROM pg_stat_all_tables
WHERE relname = 'cards';

3) SELECT n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd
FROM pg_stat_all_tables WHERE relname = 'cards';

If the table / indexes are bloated due to heavy modifications or
(auto)vacuum not aggressive enough, you may try to cluster the table.
But it obtains exclusive lock on the table, preventing writes.

Tomas