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
usingactivity_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_idon activity_follow u0 (cost=0.00..4875.15 rows=4898 width=4)
IndexCond: (user_id = 1) Filter: (content_type_id = 3) -> Index Scan
usingactivity_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"
timestampwith 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
INITIALLYDEFERRED, "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)
