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

On Tue, Mar 3, 2009 at 2:16 PM, Sebastjan Trepca <trepca(at)gmail(dot)com> wrote:
> Maybe this is useful, I removed the JOIN and it uses other
> index(core_accessor_date_idx indexes (date_posted, nooximity)), but
> its still hardly any better:
>
> noovo-new=# explain analyze SELECT * FROM "core_accessor" WHERE
> (("core_accessor"."slot_type_id" = 119
> noovo-new(#  AND "core_accessor"."slot_id" = 472 AND
> "core_accessor"."label" = E'' AND "core_accessor"."publish_state" >=
> 60 AND
> noovo-new(#  "core_accessor"."role" IN (0) AND
> "core_accessor"."user_id" = 0)) ORDER BY "core_accessor"."date_posted"
> DESC, "core_accessor"."nooximity" DESC LIMIT 5
> noovo-new-# ;
>
>       QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=0.00..3709.56 rows=5 width=178) (actual
> time=4593.867..4597.587 rows=5 loops=1)
>   ->  Index Scan Backward using core_accessor_date_idx on
> core_accessor  (cost=0.00..1810265.67 rows=2440 width=178) (actual
> time=4593.866..4597.583 rows=5 loops=1)
>         Filter: ((publish_state >= 60) AND (slot_type_id = 119) AND
> (slot_id = 472) AND (label = ''::text) AND (role = 0) AND (user_id =
> 0))
>  Total runtime: 4597.632 ms
> (4 rows)
>
>
> Sebastjan

Well, in that case, you are being bitten by the fact that our
multi-column selectivity estimates are not very good. The planner has
good information on how each column behaves in isolation, but not how
they act together. I've found this to be a very difficult problem to
fix.

Which of the parameters in this query vary and which ones are
typically always the same? Sometimes you can improve things by
creating an appropriate partial index.

...Robert