Thread: Problems with non use of indexes
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)
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)
Hi,
On 2 Březen 2012, 13:12, Tyler Durden 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
> --------------------------------------------------------------------------------------------------------
> 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.
Errr, what? The only sequential scan in that explain output is on
auth_user, not activity_follow which is the table referenced in the
original query. It actually uses index scan to read activity_follow
-> 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)
kind regards
Tomas
Hi,
On Fri, Mar 2, 2012 at 12:23 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
On Fri, Mar 2, 2012 at 12:23 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
Hi,> Scan on *U0."user_id" = 1*
On 2 Březen 2012, 13:12, Tyler Durden 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
> --------------------------------------------------------------------------------------------------------
> 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>> "activity_follow" U0 WHERE (U0."content_type_id" = 3 AND *U0."user_id"
> 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
> = 1*)) ORDER BY "activity_action"."created" DESC LIMIT 100;>Errr, what? The only sequential scan in that explain output is on
> 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.
auth_user, not activity_follow which is the table referenced in the
original query. It actually uses index scan to read activity_followkind regards
-> 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)
Tomas
Yes, but if I remove U0."user_id" = 1 will use the index:
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 )) ORDER BY "activity_action"."created" DESC LIMIT 100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..4502.18 rows=100 width=155)
-> Nested Loop (cost=0.00..2041605.23 rows=45347 width=155)
-> Nested Loop Semi Join (cost=0.00..1907985.65 rows=45347 width=55)
-> Index Scan using activity_action_created on activity_action (cost=0.00..40093.37 rows=1104800 width=51)
-> Index Scan using activity_follow_object_id on activity_follow u0 (cost=0.00..5519.13 rows=3328 width=4)
Index Cond: (u0.object_id = activity_action.actor_id)
Filter: (u0.content_type_id = 3)
-> Index Scan using auth_user_pkey on auth_user (cost=0.00..2.93 rows=1 width=104)
Index Cond: (auth_user.id = activity_action.actor_id)
On 2 Březen 2012, 13:45, Tyler Durden wrote: >> > Yes, but if I remove *U0."user_id" = 1 *will use the index: Which PostgreSQL version is that? Post EXPLAIN ANALYZE output for all three queries, not just EXPLAIN. And use explain.depesz.com if possible, it's much more readable. kind regards Tomas
On Fri, Mar 2, 2012 at 12:55 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
I'm using PostgreSQL 8.4.2 and you can find the EXPLAIN ANALYSE VERBOSE in http://explain.depesz.com/s/hk2
Thanks!
On 2 Březen 2012, 13:45, Tyler Durden wrote:
>>
> Yes, but if I remove *U0."user_id" = 1 *will use the index:
Which PostgreSQL version is that? Post EXPLAIN ANALYZE output for all
three queries, not just EXPLAIN. And use explain.depesz.com if possible,
it's much more readable.
kind regards
Tomas
I'm using PostgreSQL 8.4.2 and you can find the EXPLAIN ANALYSE VERBOSE in http://explain.depesz.com/s/hk2
Thanks!
On 2 Březen 2012, 14:34, Tyler Durden wrote: > On Fri, Mar 2, 2012 at 12:55 PM, Tomas Vondra <tv@fuzzy.cz> wrote: > >> On 2 Březen 2012, 13:45, Tyler Durden wrote: >> >> >> > Yes, but if I remove *U0."user_id" = 1 *will use the index: >> >> Which PostgreSQL version is that? Post EXPLAIN ANALYZE output for all >> three queries, not just EXPLAIN. And use explain.depesz.com if possible, >> it's much more readable. >> >> kind regards >> Tomas >> > > I'm using PostgreSQL 8.4.2 and you can find the EXPLAIN ANALYSE VERBOSE in > http://explain.depesz.com/s/hk2 For all three queries, please. Tomas
http://explain.depesz.com/s/f92O
EXPLAIN ANALYSE VERBOSE 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;
---
http://explain.depesz.com/s/o3w
EXPLAIN ANALYSE VERBOSE 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)) ORDER BY "activity_action"."created" DESC LIMIT 100;
---
http://explain.depesz.com/s/ccJ
EXPLAIN ANALYSE VERBOSE SELECT U0."object_id" FROM "activity_follow" U0 WHERE (U0."content_type_id" = 3 AND U0."user_id" = 1);
EXPLAIN ANALYSE VERBOSE 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;
---
http://explain.depesz.com/s/o3w
EXPLAIN ANALYSE VERBOSE 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)) ORDER BY "activity_action"."created" DESC LIMIT 100;
---
http://explain.depesz.com/s/ccJ
EXPLAIN ANALYSE VERBOSE SELECT U0."object_id" FROM "activity_follow" U0 WHERE (U0."content_type_id" = 3 AND U0."user_id" = 1);
On Fri, Mar 2, 2012 at 2:23 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
For all three queries, please.On 2 Březen 2012, 14:34, Tyler Durden wrote:
> On Fri, Mar 2, 2012 at 12:55 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>
>> On 2 Březen 2012, 13:45, Tyler Durden wrote:
>> >>
>> > Yes, but if I remove *U0."user_id" = 1 *will use the index:
>>
>> Which PostgreSQL version is that? Post EXPLAIN ANALYZE output for all
>> three queries, not just EXPLAIN. And use explain.depesz.com if possible,
>> it's much more readable.
>>
>> kind regards
>> Tomas
>>
>
> I'm using PostgreSQL 8.4.2 and you can find the EXPLAIN ANALYSE VERBOSE in
> http://explain.depesz.com/s/hk2
Tomas
On Fri, Mar 2, 2012 at 5:12 AM, Tyler Durden <tylersticky@gmail.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.