Re: Problems with non use of indexes

On Fri, Mar 2, 2012 at 5:12 AM, Tyler Durden <tylersticky(at)gmail(dot)com> wrote:
> Hi,
> I can't figure out why query planner doesn't use the proper index, anyone
> can help me?
>
> This query properly uses indexes:
>
> mydb=# EXPLAIN SELECT U0."object_id" FROM "activity_follow" U0 WHERE
> (U0."content_type_id" = 3 AND U0."user_id" = 1);

Query plan: http://explain.depesz.com/s/ccJ
No order by in the above. Order by in the below:

> mydb=# EXPLAIN SELECT "activity_action"."id", "activity_action"."actor_id",
> "activity_action"."verb", "activity_action"."action_content_type_id",
> "activity_action"."action_object_id",
> "activity_action"."target_content_type_id",
> "activity_action"."target_object_id", "activity_action"."public",
> "activity_action"."created", "auth_user"."id", "auth_user"."username",
> "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email",
> "auth_user"."password", "auth_user"."is_staff", "auth_user"."is_active",
> "auth_user"."is_superuser", "auth_user"."last_login",
> "auth_user"."date_joined" FROM "activity_action" INNER JOIN "auth_user" ON
> ("activity_action"."actor_id" = "auth_user"."id") WHERE
> "activity_action"."actor_id" IN (SELECT U0."object_id" FROM
> "activity_follow" U0 WHERE (U0."content_type_id" = 3 AND U0."user_id" = 1 ))
> ORDER BY "activity_action"."created" DESC LIMIT 100;

query plan: http://explain.depesz.com/s/f92O

What happens if you drop the order by on it? Just for comparison.
I'm guessing that needing to sort is where the cost is coming from.