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

Re: Query Optimizer makes a poor choice

From: Tomas Vondra <tv(at)fuzzy(dot)cz>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Query Optimizer makes a poor choice
Date: 2011-11-29 22:28:01
Message-ID: 4ED55C71.9050309@fuzzy.cz (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Merlin MoncureDate: 2011-11-29 22:38:01
Subject: Re: Limiting number of connections to PostgreSQL per IP (not per DB/user)?
Previous:From: Tomas VondraDate: 2011-11-29 22:19:43
Subject: Re: Query Optimizer makes a poor choice

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