also thanks for your answer!
I also believe that the generated query is more than suboptimal. Here it is.
I've not deleted any columns, since there is something strange about the GROUP BY clause: all columns are listed there twice:
SELECT "events_event"."id",
"events_event"."uid",
"events_event"."slug_de",
"events_event"."slug_en",
"events_event"."created_by_id",
"events_event"."created_on",
"events_event"."modified_by_id",
"events_event"."modified_on",
"events_event"."status",
"events_event"."online_from",
"events_event"."title_de",
"events_event"."subtitle_de",
"events_event"."description_de",
"events_event"."title_en",
"events_event"."subtitle_en",
"events_event"."description_en",
"events_event"."image_id",
"events_event"."location_id",
"events_event"."room_id",
"events_event"."contact_display",
"events_event"."price_from",
"events_event"."price_to",
"events_event"."vienna_card",
"events_event"."use_address",
"events_event"."use_tel_1",
"events_event"."use_tel_2",
"events_event"."use_tel_3",
"events_event"."use_email",
"events_event"."use_web",
"events_event"."alt_contact",
"events_event"."alt_contact_use",
"events_event"."alt_tel_1",
"events_event"."alt_tel_1_comment_de",
"events_event"."alt_tel_1_comment_en",
"events_event"."alt_tel_1_use",
"events_event"."alt_tel_2",
"events_event"."alt_tel_2_comment_de",
"events_event"."alt_tel_2_comment_en",
"events_event"."alt_tel_2_use",
"events_event"."alt_tel_3",
"events_event"."alt_tel_3_comment_de",
"events_event"."alt_tel_3_comment_en",
"events_event"."alt_tel_3_use",
"events_event"."alt_email_1",
"events_event"."alt_email_1_use",
"events_event"."alt_email_2",
"events_event"."alt_email_2_use",
"events_event"."alt_email_3",
"events_event"."alt_email_3_use",
"events_event"."alt_url_1",
"events_event"."alt_url_1_use",
"events_event"."alt_url_2",
"events_event"."alt_url_2_use",
"events_event"."alt_url_3",
"events_event"."alt_url_3_use",
"events_event"."ranking",
"events_event"."toptip",
"events_event"."daily_top",
"events_event"."term",
"events_event"."legacy_id", MAX("events_eventdate"."date") AS "eventdate_max"
FROM "events_event"
LEFT OUTER JOIN "events_eventdate" ON ("events_event"."id" = "events_eventdate"."event_id")
WHERE ("events_event"."status" = 2)
GROUP BY "events_event"."id",
"events_event"."uid",
"events_event"."slug_de",
"events_event"."slug_en",
"events_event"."created_by_id",
"events_event"."created_on",
"events_event"."modified_by_id",
"events_event"."modified_on",
"events_event"."status",
"events_event"."online_from",
"events_event"."title_de",
"events_event"."subtitle_de",
"events_event"."description_de",
"events_event"."title_en",
"events_event"."subtitle_en",
"events_event"."description_en",
"events_event"."image_id",
"events_event"."location_id",
"events_event"."room_id",
"events_event"."contact_display",
"events_event"."price_from",
"events_event"."price_to",
"events_event"."vienna_card",
"events_event"."use_address",
"events_event"."use_tel_1",
"events_event"."use_tel_2",
"events_event"."use_tel_3",
"events_event"."use_email",
"events_event"."use_web",
"events_event"."alt_contact",
"events_event"."alt_contact_use",
"events_event"."alt_tel_1",
"events_event"."alt_tel_1_comment_de",
"events_event"."alt_tel_1_comment_en",
"events_event"."alt_tel_1_use",
"events_event"."alt_tel_2",
"events_event"."alt_tel_2_comment_de",
"events_event"."alt_tel_2_comment_en",
"events_event"."alt_tel_2_use",
"events_event"."alt_tel_3",
"events_event"."alt_tel_3_comment_de",
"events_event"."alt_tel_3_comment_en",
"events_event"."alt_tel_3_use",
"events_event"."alt_email_1",
"events_event"."alt_email_1_use",
"events_event"."alt_email_2",
"events_event"."alt_email_2_use",
"events_event"."alt_email_3",
"events_event"."alt_email_3_use",
"events_event"."alt_url_1",
"events_event"."alt_url_1_use",
"events_event"."alt_url_2",
"events_event"."alt_url_2_use",
"events_event"."alt_url_3",
"events_event"."alt_url_3_use",
"events_event"."ranking",
"events_event"."toptip",
"events_event"."daily_top",
"events_event"."term",
"events_event"."legacy_id",
"events_event"."id", #### duplicate fields starting here
"events_event"."uid",
"events_event"."slug_de",
"events_event"."slug_en",
"events_event"."created_by_id",
"events_event"."created_on",
"events_event"."modified_by_id",
"events_event"."modified_on",
"events_event"."status",
"events_event"."online_from",
"events_event"."title_de",
"events_event"."subtitle_de",
"events_event"."description_de",
"events_event"."title_en",
"events_event"."subtitle_en",
"events_event"."description_en",
"events_event"."image_id",
"events_event"."location_id",
"events_event"."room_id",
"events_event"."contact_display",
"events_event"."price_from",
"events_event"."price_to",
"events_event"."vienna_card",
"events_event"."use_address",
"events_event"."use_tel_1",
"events_event"."use_tel_2",
"events_event"."use_tel_3",
"events_event"."use_email",
"events_event"."use_web",
"events_event"."alt_contact",
"events_event"."alt_contact_use",
"events_event"."alt_tel_1",
"events_event"."alt_tel_1_comment_de",
"events_event"."alt_tel_1_comment_en",
"events_event"."alt_tel_1_use",
"events_event"."alt_tel_2",
"events_event"."alt_tel_2_comment_de",
"events_event"."alt_tel_2_comment_en",
"events_event"."alt_tel_2_use",
"events_event"."alt_tel_3",
"events_event"."alt_tel_3_comment_de",
"events_event"."alt_tel_3_comment_en",
"events_event"."alt_tel_3_use",
"events_event"."alt_email_1",
"events_event"."alt_email_1_use",
"events_event"."alt_email_2",
"events_event"."alt_email_2_use",
"events_event"."alt_email_3",
"events_event"."alt_email_3_use",
"events_event"."alt_url_1",
"events_event"."alt_url_1_use",
"events_event"."alt_url_2",
"events_event"."alt_url_2_use",
"events_event"."alt_url_3",
"events_event"."alt_url_3_use",
"events_event"."ranking",
"events_event"."toptip",
"events_event"."daily_top",
"events_event"."term",
"events_event"."legacy_id" HAVING MAX("events_eventdate"."date") < '2011-03-29'
ORDER BY "events_event"."title_de" ASC, "events_event"."status" ASC LIMIT 21
I also don't know what the LIMIT 21 is there for.
As Javier suggested, an index on the "events_eventdate" over table over "event_id" and "date" could help, but I don't know how to create one using Django's model techniques except for a unique-together index, which I cannot use because the two fields are not unique together.
Thanks for any hints on this!
Regards,
Fabian
2011/3/28 akaariai <akaariai@gmail.com>
The easiest way to see which indexes help is to get the generated SQL
On Mar 28, 3:52 pm, Fabian Büchler <fabian.buech...@gmail.com> wrote:
> Now this query takes about 15 seconds to run on a database with about 5,000
> Events and 50,000 EventDates.
> I'm running Django 1.3 (trunk) and PostgreSQL 9.0 on a relatively recent
> quadcore machine.
>
> Is there any way to do a more efficient query?
of the query and then play with PostgreSQL directly in dbshell, or if
you want a GUI, use PgAdmin. You can get the sql with
str(queryset.query). Then try to create different indexes so that the
query will be as fast as possible. I do not know what kind of SQL the
above code will generate, but even in the worst case (the generated
SQL is suboptimal) this should be relatively easy to write in raw SQL
if needed.
If you post the generated SQL here I will try to help find the right
indexes.
But I must say that having a runtime of 15 seconds for so little data
feels like the generated query could be suboptimal, or there could be
something else strange going on, like the PostgreSQL statistics for
the tables being off. Did you load the data just before running this
query? If that is the case try to run "vacuum analyze;" in dbshell
before testing.
I did a little test and without any indexes I could get a runtime of
100 milliseconds for a hand written query doing essentially the same
thing. And this is using an old laptop...
- Anssi
--
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+unsubscribe@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+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
No comments:
Post a Comment