Problems with non use of indexes - Mailing list pgsql-general

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                                              
--------------------------------------------------------------------------------------------------------
 Index Scan using activity_follow_user_id on activity_follow u0  (cost=0.00..4875.15 rows=4898 width=4)
   Index Cond: (user_id = 1)
   Filter: (content_type_id = 3)
(3 rows)

But the same query on a "IN" statement doesn't. The query planner uses Seq Scan on U0."user_id" = 1

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                                                             
--------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=9206.97..9207.22 rows=100 width=155)
   ->  Sort  (cost=9206.97..9320.34 rows=45347 width=155)
         Sort Key: activity_action.created
         ->  Hash Join  (cost=5447.39..7473.84 rows=45347 width=155)
               Hash Cond: (activity_action.actor_id = auth_user.id)
               ->  Nested Loop  (cost=4887.39..5020.58 rows=45347 width=55)
                     ->  HashAggregate  (cost=4887.39..4887.41 rows=2 width=4)
                           ->  Index Scan using activity_follow_user_id on activity_follow u0  (cost=0.00..4875.15 rows=4898 width=4)
                                 Index Cond: (user_id = 1)
                                 Filter: (content_type_id = 3)
                     ->  Index Scan using activity_action_actor_id on activity_action  (cost=0.00..65.20 rows=111 width=51)
                           Index Cond: (activity_action.actor_id = u0.object_id)
               ->  Hash  (cost=278.00..278.00 rows=10000 width=104)
                     ->  Seq Scan on auth_user  (cost=0.00..278.00 rows=10000 width=104)


If I do a SET enable_seqscan TO 'off'; It uses the index but is also slow.

---

TABLES:

CREATE TABLE "auth_user" (
    "id" serial NOT NULL PRIMARY KEY,
    "username" varchar(30) NOT NULL UNIQUE,
    "first_name" varchar(30) NOT NULL,
    "last_name" varchar(30) NOT NULL,
    "email" varchar(75) NOT NULL,
    "password" varchar(128) NOT NULL,
    "is_staff" boolean NOT NULL,
    "is_active" boolean NOT NULL,
    "is_superuser" boolean NOT NULL,
    "last_login" timestamp with time zone NOT NULL,
    "date_joined" timestamp with time zone NOT NULL
);

CREATE TABLE "activity_follow" (
    "id" serial NOT NULL PRIMARY KEY,
    "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
    "content_type_id" integer NOT NULL REFERENCES "django_content_type" ("id") DEFERRABLE INITIALLY DEFERRED,
    "object_id" integer CHECK ("object_id" >= 0),
    UNIQUE ("user_id", "content_type_id", "object_id")
);

CREATE TABLE "activity_action" (
    "id" serial NOT NULL PRIMARY KEY,
    "actor_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
    "verb" varchar(50) NOT NULL,
    "action_content_type_id" integer REFERENCES "django_content_type" ("id") DEFERRABLE INITIALLY DEFERRED,
    "action_object_id" integer CHECK ("action_object_id" >= 0),
    "target_content_type_id" integer REFERENCES "django_content_type" ("id") DEFERRABLE INITIALLY DEFERRED,
    "target_object_id" integer CHECK ("target_object_id" >= 0),
    "public" boolean NOT NULL,
    "created" timestamp with time zone NOT NULL
);

CREATE INDEX "activity_follow_user_id" ON "activity_follow" ("user_id");
CREATE INDEX "activity_follow_content_type_id" ON "activity_follow" ("content_type_id");
CREATE INDEX "activity_follow_object_id" ON "activity_follow" ("object_id");
CREATE INDEX "activity_action_actor_id" ON "activity_action" ("actor_id");
CREATE INDEX "activity_action_action_content_type_id" ON "activity_action" ("action_content_type_id");
CREATE INDEX "activity_action_action_object_id" ON "activity_action" ("action_object_id");
CREATE INDEX "activity_action_target_content_type_id" ON "activity_action" ("target_content_type_id");
CREATE INDEX "activity_action_target_object_id" ON "activity_action" ("target_object_id");

---

mydb=# SELECT COUNT(1) FROM activity_action;
  count 
---------
 1104800
(1 row)

mydb=# SELECT COUNT(1) FROM activity_follow;
  count 
---------
 1104800
(1 row)

mydb=# SELECT COUNT(1) FROM auth_user;
 count
-------
 10000
(1 row)