From: "tylersticky@gmail.com" <tylersticky@gmail.com>
Subject: Problems with non use of indexes
Date: Fri, 2 Mar 2012 04:17:20 -0800 (PST)
Organization: http://groups.google.com
Lines: 138
Message-ID: <13164632.772.1330690640934.JavaMail.geo-discussion-forums@vbkc1>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com;
 posting-host=193.203.106.18;
	posting-account=uileRgoAAADSaeLSwhpT9N0TAokIF-WX
User-Agent: G2/1.0
To: pgsql-sql@postgresql.org

Hi,
I can't figure out why query planner doesn't use the proper index, anyone c=
an help me?

This query properly uses indexes:

mydb=3D# EXPLAIN SELECT U0."object_id" FROM "activity_follow" U0 WHERE (U0.=
"content_type_id" =3D 3 AND U0."user_id" =3D 1);

QUERY PLAN                                             =20
---------------------------------------------------------------------------=
-----------------------------
 Index Scan using activity_follow_user_id on activity_follow u0  (cost=3D0.=
00..4875.15 rows=3D4898 width=3D4)
   Index Cond: (user_id =3D 1)
   Filter: (content_type_id =3D 3)
(3 rows)

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

mydb=3D# EXPLAIN SELECT "activity_action"."id", "activity_action"."actor_id=
", "activity_action"."verb", "activity_action"."action_content_type_id", "a=
ctivity_action"."action_object_id", "activity_action"."target_content_type_=
id", "activity_action"."target_object_id", "activity_action"."public", "act=
ivity_action"."created", "auth_user"."id", "auth_user"."username", "auth_us=
er"."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 "ac=
tivity_action" INNER JOIN "auth_user" ON ("activity_action"."actor_id" =3D =
"auth_user"."id") WHERE "activity_action"."actor_id" IN (SELECT U0."object_=
id" FROM "activity_follow" U0 WHERE (U0."content_type_id" =3D 3 AND U0."use=
r_id" =3D 1 )) ORDER BY "activity_action"."created" DESC LIMIT 100;

QUERY PLAN                                                            =20
---------------------------------------------------------------------------=
-----------------------------------------------------------
 Limit  (cost=3D9206.97..9207.22 rows=3D100 width=3D155)
   ->  Sort  (cost=3D9206.97..9320.34 rows=3D45347 width=3D155)
         Sort Key: activity_action.created
         ->  Hash Join  (cost=3D5447.39..7473.84 rows=3D45347 width=3D155)
               Hash Cond: (activity_action.actor_id =3D auth_user.id)
               ->  Nested Loop  (cost=3D4887.39..5020.58 rows=3D45347 width=
=3D55)
                     ->  HashAggregate  (cost=3D4887.39..4887.41 rows=3D2 w=
idth=3D4)
                           ->  Index Scan using activity_follow_user_id on =
activity_follow u0  (cost=3D0.00..4875.15 rows=3D4898 width=3D4)
                                 Index Cond: (user_id =3D 1)
                                 Filter: (content_type_id =3D 3)
                     ->  Index Scan using activity_action_actor_id on activ=
ity_action  (cost=3D0.00..65.20 rows=3D111 width=3D51)
                           Index Cond: (activity_action.actor_id =3D u0.obj=
ect_id)
               ->  Hash  (cost=3D278.00..278.00 rows=3D10000 width=3D104)
                     ->  Seq Scan on auth_user  (cost=3D0.00..278.00 rows=
=3D10000 width=3D104)


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 INI=
TIALLY DEFERRED,
    "content_type_id" integer NOT NULL REFERENCES "django_content_type" ("i=
d") DEFERRABLE INITIALLY DEFERRED,
    "object_id" integer CHECK ("object_id" >=3D 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 IN=
ITIALLY 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" >=3D 0),
    "target_content_type_id" integer REFERENCES "django_content_type" ("id"=
) DEFERRABLE INITIALLY DEFERRED,
    "target_object_id" integer CHECK ("target_object_id" >=3D 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" ("conte=
nt_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" ("acti=
on_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" ("targ=
et_object_id");

---

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

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

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