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.

So I have the following query

explain analyze
with tags as (
    select unnest(tags) as tag_name from tasks where user_id = 1
) select 
        count(9), 
        tag_name
    from 
        tags
    group by
        tag_name
    order by 
        count(9) desc
    limit 50

Gives me the following result:

Limit  (cost=3243.86..3243.99 rows=50 width=32) (actual time=2.278..2.278 rows=1 loops=1)
  CTE tags
    ->  Bitmap Heap Scan on tasks  (cost=12.35..1917.72 rows=52700 width=13) (actual time=0.098..2.074 rows=261 loops=1)
          Recheck Cond: (user_id = 1)
          ->  Bitmap Index Scan on index_tasks_user_id  (cost=0.00..12.22 rows=527 width=0) (actual time=0.065..0.065 rows=261 loops=1)
                Index Cond: (user_id = 1)
  ->  Sort  (cost=1326.14..1326.64 rows=200 width=32) (actual time=2.278..2.278 rows=1 loops=1)
        Sort Key: (count(9))
        Sort Method: quicksort  Memory: 25kB
        ->  HashAggregate  (cost=1317.50..1319.50 rows=200 width=32) (actual time=2.273..2.274 rows=1 loops=1)
              ->  CTE Scan on tags  (cost=0.00..1054.00 rows=52700 width=32) (actual time=0.099..2.177 rows=261 loops=1)
Total runtime: 2.314 ms

Which is pretty decent I suppose. The previous way of doing things where to have a bunch of join tables and that gave me something like below:

Limit  (cost=919.38..919.40 rows=50 width=12) (actual time=163.164..163.257 rows=50 loops=1)
  ->  Sort  (cost=919.38..919.48 rows=206 width=12) (actual time=163.162..163.194 rows=50 loops=1)
        Sort Key: (count(*))
        Sort Method: top-N heapsort  Memory: 28kB
        ->  HashAggregate  (cost=917.39..918.01 rows=206 width=12) (actual time=162.899..163.008 rows=132 loops=1)
              ->  Nested Loop  (cost=456.90..917.19 rows=206 width=12) (actual time=1.040..162.361 rows=416 loops=1)
                    ->  Hash Join  (cost=456.90..904.32 rows=206 width=4) (actual time=1.029..159.429 rows=416 loops=1)
                          Hash Cond: (taggings.workout_id = workouts.id)
                          ->  Seq Scan on taggings  (cost=0.00..416.64 rows=40214 width=8) (actual time=0.010..45.753 rows=37029 loops=1)
                          ->  Hash  (cost=455.91..455.91 rows=282 width=4) (actual time=1.004..1.004 rows=293 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 11kB
                                ->  Bitmap Heap Scan on workouts  (cost=4.49..455.91 rows=282 width=4) (actual time=0.101..0.744 rows=293 loops=1)
                                      Recheck Cond: (user_id = 1)
                                      ->  Bitmap Index Scan on index_workouts_on_user_id  (cost=0.00..4.48 rows=282 width=0) (actual time=0.058..0.058 rows=293 loops=1)
                                            Index Cond: (user_id = 1)
                    ->  Index Scan using tags_pkey on tags  (cost=0.00..0.06 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=416)
                          Index Cond: (id = taggings.tag_id)
Total runtime: 163.393 ms

Now forget about the last explain and lets focus on the first one. Can it be optimized further? Any tricks or such that I might be missing out on? I guess an index on the user_id column should be plenty for this query?

share|improve this question
    
Apart from the fact that the original and the present queries differ not only in their respective execution plans but the whole logic behind them (the Limit part clearly shows that), and this way you compare apples to cars here, it really helps to disclose the aim of your query in plain English. Now I think it is counting for each tag how many times it is assigned to different tasks. Am I right? Anyway, I am pretty sure you can further optimize your query since it returns exactly zero rows at the moment :-o –  dezso Feb 14 '13 at 19:34
    
First of all I'm not comparing the two I just threw the second query plan in there because that's what I had before. I updated the question with comparable query plans but my question is still if it's possible to improve the query more than the obvious index on the user_id. –  mhenrixon Feb 14 '13 at 20:23
add comment

1 Answer

up vote 2 down vote accepted

I doubt that your query can be further optimized for performance. Right now it is making good use of indexes so there is not much more to be done. The only example that might be worth trying might be to move the CTE into a view but I doubt that will have much of an effect either way (it may be worth trying however, because CTE scans are somewhat special, and they are separately planned, which makes no change in your favor here).

One thing I would point out is that there are a few things that can be done for readability. In PostgreSQL, the convention is to use "count(*)" which the planner knows to mean to just count the number of rows returned. This does not use up more memory than count(1) or count(9) and it is a lot more readable. With an expression like count(9), I encounter it so seldom, I actually have to stop and think about what it means, which is not a good thing when it comes to queries.

share|improve this answer
    
Thanks a lot for the pointers, I got used to writing count(9) when working with Jeeves, it was for MS SQL Server but the convention sort of stuck with me. –  mhenrixon Feb 27 '13 at 7:07
    
In the query plan all the row estimates are far off from actual numbers - don't you think a few ANALYZE commands would make things (maybe only slightly) faster? –  dezso Feb 27 '13 at 7:39
    
I am not sure that would help for two reasons: 1. The estimates are off on a scan which involves unnesting an array. I am not sure the planner has detailed knowledge of array internals enough for a good estimate there, and 2. The query plan is pretty good. I am not really that there is any opportunity for a better one. The second query plan might benefit, but there I am not sure either since in absolute numbers, it isn't that far off. –  Chris Travers Feb 27 '13 at 9:41
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.