Need your advices to optimize when annotate foreign key

[Sardor Muminov]

Hello there,

I am trying to perform aggregation from all objects which have one foreign key.

These are my models:

...

class Post(models.Model):

    id = models.CharField(max_length=255, primary_key=True)                                                                                                                     

    message = models.TextField()

    created_time = models.DateTimeField(db_index=True)

    updated_time = models.DateTimeField()

    creator = models.ForeignKey(Member, db_index=True)

    likes = models.IntegerField(db_index=True)

class Comment(models.Model):

    id = models.CharField(max_length=255, primary_key=True, db_index=True)

    message = models.TextField()

    created_time = models.DateTimeField(db_index=True)

    creator = models.ForeignKey(Member, db_index=True)

    post = models.ForeignKey(Post, db_index=True)

    likes = models.IntegerField(db_index=True)

...

And this is my query:

top_commented_posts = Post.objects.annotate(num_comments=Count('comment')).order_by('-num_comments')[:10]

In my template, I iterate over top_commented_posts:

...

{% for post in top_commented_posts %}

    <tr> 

        <td>{{ forloop.counter }}.</td>

        <td>{{ post.message | slice:"25"}}...</td>

        <td>{{ post.num_comments }}</td>

        <td><a class="btn btn-xs btn-inverse" href="https://www.mydomain.com/{{ post.id }}/" target="_blank"><span class="icon-link"></span></a></td>

    </tr>

{% endfor %}

...

This operations is one of the 27 ones from one page.

Django debug toolbar result:

5335.56 ms (27 queries)

26 each queries taking between 1.44ms (min) and 227ms (max).

Please, share your experience and advices on how can I optimize this query.

I have also attached Django debug toolbar screenshot.

Thanks in advance,

Sardor

[tkdchen]

On Monday, March 16, 2015 at 5:19:38 PM UTC+8, Sardor Muminov wrote:

Hello there,

I am trying to perform aggregation from all objects which have one foreign key.

These are my models:

...

class Post(models.Model):

    id = models.CharField(max_length=255, primary_key=True)                                                                                                                     

    message = models.TextField()

    created_time = models.DateTimeField(db_index=True)

    updated_time = models.DateTimeField()

    creator = models.ForeignKey(Member, db_index=True)

    likes = models.IntegerField(db_index=True)

class Comment(models.Model):

    id = models.CharField(max_length=255, primary_key=True, db_index=True)

    message = models.TextField()

    created_time = models.DateTimeField(db_index=True)

    creator = models.ForeignKey(Member, db_index=True)

    post = models.ForeignKey(Post, db_index=True)

    likes = models.IntegerField(db_index=True)

...

And this is my query:

top_commented_posts = Post.objects.annotate(num_comments=Count('comment')).order_by('-num_comments')[:10]

top_commented_posts = Comment.objects.values('post').annotrate(post_count=Count('pk')).order_by('-post_count')

The rest is to construct above result with Posts to display in template.

Regards,
Chenxiong Qi
 

[Simon Charette]

Hi Sardor,

Are you using PostgreSQL?

Simon

[Sardor Muminov]

Hi Simon.

Yes I am. I forgot to mention it. I am using PostgreSQL 9.3.6

Now I am implementing Chenxiong Qi's suggestion.

Do you have any idea?

[Simon Charette]

Hi Sardor,

I think you've hit a long standing bug: #19259 Annotations generating inefficient SQL on PostgreSQL

Simon