Re: Problems with ordering (can't force query planner to use an index)

On Tue, Mar 3, 2009 at 12:05 PM, Sebastjan Trepca <trepca(at)gmail(dot)com> wrote:

>         ->  Nested Loop  (cost=0.00..31883.13 rows=2861 width=860)
> (actual time=0.089..543.497 rows=68505 loops=1)
>               ->  Index Scan using core_accessor_fresh_idx on
> core_accessor  (cost=0.00..5460.07 rows=2970 width=92) (actual
> time=0.068..54.921 rows=69312 loops=1)
>                     Index Cond: ((slot_id = 472) AND (slot_type_id =
> 119) AND (label = ''::text) AND (user_id = 0) AND (role = 0) AND
> (publish_state >= 60))

That index scan on core_accessor_fresh_idx has a pretty big disparity
between what the planer expects to get (2970 rows) and what it
actually gets (69312 rows). You should try increasing the statistics
target if you haven't, then re-analyze and try the query again to see
if the planner picks something better. The default of 10 is pretty
small- try 100, or higher.

--
- David T. Wilson
david(dot)t(dot)wilson(at)gmail(dot)com