I am using Django Debug Toolbar locally for development. That is where I copied & pasted the SQL from.
Even if I had a complete set of data for postgresql, my local machine is an 8-core, 16GB of RAM, with solid state hard drives. A LOT different than my 2CPU/1GB RAM/spindle disk VPS. I guess what I need to do is clone my VPS off, firewall the crap out of it and run Django Debug Toolbar on that...giving me a more accurate idea of what's going on. Thanks for the response. On Fri, Oct 19, 2012 at 10:25 PM, Sam Lai <[email protected]> wrote: > On 20 October 2012 14:16, Barry Morrison <[email protected]> wrote: > > I've got a pretty expensive query...Wondering if I can't speed things up > in > > regards to Postgresql. > > > > Here is the model: [1] http://dpaste.org/JmEeQ/ > > > > Here is the sql statement: [2] http://dpaste.org/GbfAJ/ > > > > Here is the template: [3] http://dpaste.org/vxRs4/ > > > > Here is the 'guts' of the view: [4] http://dpaste.org/w0b2z/ > > > > Total Postgresql/SQL n00b, so this may be a stupid statement. I'm > wondering > > if an index on '"press_page"."article_id"' wouldn't speed things up a > bit? > > Are you using the django-debug-toolbar? If not, consider using it - > once installed, visit that page in your browser, open up the toolbar, > select SQL queries, find the offending query (processing times are > shown), and click the Explain link. That will tell you what PostgreSQL > is doing, and how performance can be improved. > > Alternately, fire up a terminal and launch the psql database shell. > Type in the query ([2]) but prefix it with the word EXPLAIN. This will > tell you what PostgreSQL is doing to process that query. If you see > Seq Scan in there, consider adding the index. Remember to run the SQL > statement - ANALYZE press_page - after you create the index so it can > update the internal statistics that it uses to decide how to process a > query. > > You can also do this within the PGadmin application if that makes things > easier. > > > In dev, I don't have the amount of data that exists in production, so I > > can't easily/realistically recreate this scenario. > > You really need to have a good subset (considering both the number of > records, and the distribution of values within each record) if it is > not possible to have a copy of the production database. PostgreSQL > performs analysis on the data itself and uses those statistics to > decide how to process the query (whether to use an index, scan > sequentially etc.). If you dev database is not a good representation, > then the results you'll see with your dev database, and hence your > improvements, may have no effect or a negative effect in production. > > I do believe having an index on press_page.article_id is a good idea > though. Also, are you really showing every article in that view? > Consider using a limit so you're not retrieving all the records, only > to use the first 10. The pagination features in Django may be useful > here. Finally, an index on press_page.pgnumber may also be worth > considering so PostgreSQL may be able to avoid the sorting step (an > index of the default type is sorted by the field in ascending order). > > > FWIW, I'm running: > > > > Django 12.04 > > Postgresql 9.1.5 > > Django 1.4.1 > > > > I'm using memcache on the view to cache it, it helped take page load down > > significantly, but I was wondering if there wasn't something I could do > from > > the DB side of things since I know it's this query that is taking the > page > > so long to load. > > > > Thanks! > > > > -- > > You received this message because you are subscribed to the Google Groups > > "Django users" group. > > To view this discussion on the web visit > > https://groups.google.com/d/msg/django-users/-/U92OjnMgEggJ. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group at > > http://groups.google.com/group/django-users?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "Django users" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/django-users?hl=en. > > -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
