Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'd like to get a count(*) from a relatively small joined table (returned result is about ~30000 rows). I use Postgresql 9.3 so simply getting select count(*) from tasks_task; uses index-only scan, but joined query performs seq scan instead.

Simple-case scenario: explain analyze select count(*) from tasks_task;

Aggregate  (cost=875.99..876.00 rows=1 width=0) (actual time=6.284..6.284 rows=1 loops=1)
->  Index Only Scan using tasks_task_id on tasks_task  (cost=0.29..800.03 rows=30383 width=0) (actual time=0.042..3.944 rows=30383 loops=1)
     Heap Fetches: 0
Total runtime: 6.366 ms

The actual query:

SELECT COUNT(*)
FROM "tasks_task"
LEFT OUTER JOIN "taskaccess_groupaccessible" ON ("tasks_task"."id" = "taskaccess_groupaccessible"."task_id")
LEFT OUTER JOIN "taskaccess_accessible" ON ("tasks_task"."id" = "taskaccess_accessible"."task_id")
LEFT OUTER JOIN "divisions_topic" ON ("tasks_task"."topic_id" = "divisions_topic"."id")
INNER JOIN "tasks_task_unread_by_users" ON ("tasks_task"."id" = "tasks_task_unread_by_users"."task_id")
WHERE (("taskaccess_groupaccessible"."group_id" IN
          (SELECT U0."codename"
           FROM "user_group" U0
           INNER JOIN "user_group_users" U1 ON (U0."codename" = U1."group_id")
           WHERE U1."user_id" = 102)
        OR "taskaccess_accessible"."user_id" = 102
        OR "tasks_task"."topic_id" IN
          (SELECT U0."id"
           FROM "divisions_topic" U0
           LEFT OUTER JOIN "taskaccess_grouptopicaccessible" U1 ON (U0."id" = U1."topic_id")
           LEFT OUTER JOIN "taskaccess_topicaccessible" U3 ON (U0."id" = U3."topic_id")
           WHERE (U1."group_id" IN
                    (SELECT U0."codename"
                     FROM "user_group" U0
                     INNER JOIN "user_group_users" U1 ON (U0."codename" = U1."group_id")
                     WHERE U1."user_id" = 102)
                  OR U3."user_id" = 102)))
       AND "divisions_topic"."archived" = FALSE
       AND "divisions_topic"."is_template" = FALSE
       AND "tasks_task_unread_by_users"."user_id" = 102);

Explain: http://explain.depesz.com/s/2cT

That's the most troubling row, I guess:

Seq Scan on tasks_task (cost=0.00..10568.79 rows=30382 width=8) (actual time=0.052..28.744 rows=30383 loops=1)
Filter: (id <> 0)

Every column used in the query is indexed. I don't understand why index is not used at all (not to mention index-only scan). I tried to google possible explanations (like, the queried data set is too small and query planner decides it's more efficient to perform a seq scan), still no luck.

share|improve this question
    
Replace IN with JOINs. –  Mihai Feb 1 at 15:54
1  
@Mihai: a JOIN is not necessarily the same as a subquery with an IN condition. –  a_horse_with_no_name Feb 1 at 15:57
    
And, I'm afraid, subqueries are not the issue: if I remove them and leave only WHERE "tasks_task_unread_by_users"."user_id" = 102, I still get the same picture. –  rocknrollnerd Feb 1 at 16:08
    
Try a combined index on tasks_task(id, topic_id) and see if that improves things. –  a_horse_with_no_name Feb 1 at 16:25
    
Wow, it actually does: now I've got index-only search on tasks_task, thanks. The query runs for about 40 ms now, which is, I guess, sufficient (the rest of time is consumed by joins) –  rocknrollnerd Feb 1 at 17:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.