From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Tyler Durden <tylersticky(at)gmail(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Problems with non use of indexes |
Date: | 2012-03-03 12:27:08 |
Message-ID: | CAOR=d=0GPc+HUCQHE_=jhMcBFgLVg_ThJjg0Hpsjp=biOQvj8A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | exclusion | 2012-03-03 18:44:37 | BUG #6510: A simple prompt is displayed using wrong charset |
Previous Message | Scott Marlowe | 2012-03-03 12:17:19 | Re: what Linux to run |