Need your advices to optimize when annotate foreign key

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9d06aeb3-3cce-4394-9a9b-d0c80240a9c2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

screenshot.png (392K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Need your advices to optimize when annotate foreign key

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
 
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="<a href="https://www.mydomain.com/%7B%7B" target="_blank" rel="nofollow" onmousedown="this.href='https://www.google.com/url?q\75https%3A%2F%2Fwww.mydomain.com%2F%257B%257B\46sa\75D\46sntz\0751\46usg\75AFQjCNFWprkCjiAYjmKKKtrzBWWtDOyq1w';return true;" onclick="this.href='https://www.google.com/url?q\75https%3A%2F%2Fwww.mydomain.com%2F%257B%257B\46sa\75D\46sntz\0751\46usg\75AFQjCNFWprkCjiAYjmKKKtrzBWWtDOyq1w';return true;">https://www.mydomain.com/{{ <a href="http://post.id" target="_blank" rel="nofollow" onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Fpost.id\46sa\75D\46sntz\0751\46usg\75AFQjCNENbMwCHR_oS2BIpfisFlDNvniqcw';return true;" onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Fpost.id\46sa\75D\46sntz\0751\46usg\75AFQjCNENbMwCHR_oS2BIpfisFlDNvniqcw';return true;">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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/250c65e2-01e0-4591-bee4-7f8eca2e3855%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Need your advices to optimize when annotate foreign key

Simon Charette
In reply to this post by Sardor Muminov
Hi Sardor,

Are you using PostgreSQL?

Simon

Le lundi 16 mars 2015 05:19:38 UTC-4, Sardor Muminov a écrit :


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="<a href="https://www.mydomain.com/%7B%7B" target="_blank" rel="nofollow" onmousedown="this.href='https://www.google.com/url?q\75https%3A%2F%2Fwww.mydomain.com%2F%257B%257B\46sa\75D\46sntz\0751\46usg\75AFQjCNFWprkCjiAYjmKKKtrzBWWtDOyq1w';return true;" onclick="this.href='https://www.google.com/url?q\75https%3A%2F%2Fwww.mydomain.com%2F%257B%257B\46sa\75D\46sntz\0751\46usg\75AFQjCNFWprkCjiAYjmKKKtrzBWWtDOyq1w';return true;">https://www.mydomain.com/{{ <a href="http://post.id" target="_blank" rel="nofollow" onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Fpost.id\46sa\75D\46sntz\0751\46usg\75AFQjCNENbMwCHR_oS2BIpfisFlDNvniqcw';return true;" onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Fpost.id\46sa\75D\46sntz\0751\46usg\75AFQjCNENbMwCHR_oS2BIpfisFlDNvniqcw';return true;">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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/a6c163eb-c782-4ecf-9d56-c0039469bab7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Need your advices to optimize when annotate foreign key

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?


On Tuesday, March 17, 2015 at 12:25:00 AM UTC+9, Simon Charette wrote:
Hi Sardor,

Are you using PostgreSQL?

Simon

Le lundi 16 mars 2015 05:19:38 UTC-4, Sardor Muminov a écrit :


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="<a href="https://www.mydomain.com/%7B%7B" rel="nofollow" target="_blank" onmousedown="this.href='https://www.google.com/url?q\75https%3A%2F%2Fwww.mydomain.com%2F%257B%257B\46sa\75D\46sntz\0751\46usg\75AFQjCNFWprkCjiAYjmKKKtrzBWWtDOyq1w';return true;" onclick="this.href='https://www.google.com/url?q\75https%3A%2F%2Fwww.mydomain.com%2F%257B%257B\46sa\75D\46sntz\0751\46usg\75AFQjCNFWprkCjiAYjmKKKtrzBWWtDOyq1w';return true;">https://www.mydomain.com/{{ <a href="http://post.id" rel="nofollow" target="_blank" onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Fpost.id\46sa\75D\46sntz\0751\46usg\75AFQjCNENbMwCHR_oS2BIpfisFlDNvniqcw';return true;" onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Fpost.id\46sa\75D\46sntz\0751\46usg\75AFQjCNENbMwCHR_oS2BIpfisFlDNvniqcw';return true;">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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/7dea739d-b2c5-43f3-8bd6-30ae22fe63f0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Need your advices to optimize when annotate foreign key

Simon Charette
Hi Sardor,

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

Simon

Le lundi 16 mars 2015 11:56:19 UTC-4, Sardor Muminov a écrit :

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?


On Tuesday, March 17, 2015 at 12:25:00 AM UTC+9, Simon Charette wrote:
Hi Sardor,

Are you using PostgreSQL?

Simon

Le lundi 16 mars 2015 05:19:38 UTC-4, Sardor Muminov a écrit :


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="<a href="https://www.mydomain.com/%7B%7B" rel="nofollow" target="_blank" onmousedown="this.href='https://www.google.com/url?q\75https%3A%2F%2Fwww.mydomain.com%2F%257B%257B\46sa\75D\46sntz\0751\46usg\75AFQjCNFWprkCjiAYjmKKKtrzBWWtDOyq1w';return true;" onclick="this.href='https://www.google.com/url?q\75https%3A%2F%2Fwww.mydomain.com%2F%257B%257B\46sa\75D\46sntz\0751\46usg\75AFQjCNFWprkCjiAYjmKKKtrzBWWtDOyq1w';return true;">https://www.mydomain.com/{{ <a href="http://post.id" rel="nofollow" target="_blank" onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Fpost.id\46sa\75D\46sntz\0751\46usg\75AFQjCNENbMwCHR_oS2BIpfisFlDNvniqcw';return true;" onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Fpost.id\46sa\75D\46sntz\0751\46usg\75AFQjCNENbMwCHR_oS2BIpfisFlDNvniqcw';return true;">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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/17f2b2dc-b9b6-49a8-8e35-97cc2131e19a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.