Hello Anssi,
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 <akaar...@gmail.com>
>
>
> 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?
>
> The easiest way to see which indexes help is to get the generated SQL
> 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+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.
