On 20 October 2012 14:16, Barry Morrison <bdmorri...@gmail.com> 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 django-users@googlegroups.com. > To unsubscribe from this group, send email to > django-users+unsubscr...@googlegroups.com. > 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 django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
