1

I'm doing this in a Django view:

def by_gear(gear):
    types = {
        'imaging_telescopes': Telescope,
        'guiding_telescopes': Telescope,
        'mounts': Mount,
        'imaging_cameras': Camera,
        'guiding_cameras': Camera,
        'focal_reducers': FocalReducer,
        'software': Software,
        'filters': Filter,
        'accessories': Accessory,
    }
    filters = reduce(operator.or_, [Q(**{'%s__gear_ptr__pk' % t: gear.pk}) for t in types])
    images = Image.objects.filter(filters).distinct()

    return images

That's because I have a gear item, and I don't know what kind of gear it is (telescope, camera, etc). It's a base model.

So I'm ORring all possibilities with that reduce. Since all the subclasses of Gear, which are also Django Models, share the pk with the base class Gear, I can do that. I know it's not very readable, and I'm not explaining it too well, but if you look at the SQL generated below, it will be clear.

I'm trying to fetch all Images taken with that piece of equipment (Gear), so see if its pk is in image__telescopes, image__imaging_cameras, and so on.

However, that ends up producing this SQL:

SELECT COUNT(DISTINCT "astrobin_image"."id") FROM "astrobin_image"

LEFT OUTER JOIN "astrobin_image_imaging_telescopes"
ON ("astrobin_image"."id" = "astrobin_image_imaging_telescopes"."image_id")

LEFT OUTER JOIN "astrobin_image_guiding_telescopes"
ON ("astrobin_image"."id" = "astrobin_image_guiding_telescopes"."image_id")

LEFT OUTER JOIN "astrobin_image_software"
ON ("astrobin_image"."id" = "astrobin_image_software"."image_id")

LEFT OUTER JOIN "astrobin_image_filters"
ON ("astrobin_image"."id" = "astrobin_image_filters"."image_id")

LEFT OUTER JOIN "astrobin_image_imaging_cameras"
ON ("astrobin_image"."id" = "astrobin_image_imaging_cameras"."image_id")

LEFT OUTER JOIN "astrobin_image_mounts"
ON ("astrobin_image"."id" = "astrobin_image_mounts"."image_id")

LEFT OUTER JOIN "astrobin_image_focal_reducers"
ON ("astrobin_image"."id" = "astrobin_image_focal_reducers"."image_id")

LEFT OUTER JOIN "astrobin_image_accessories"
ON ("astrobin_image"."id" = "astrobin_image_accessories"."image_id")

LEFT OUTER JOIN "astrobin_image_guiding_cameras"
ON ("astrobin_image"."id" = "astrobin_image_guiding_cameras"."image_id")

WHERE ("astrobin_image_imaging_telescopes"."telescope_id" = 4723
OR "astrobin_image_guiding_telescopes"."telescope_id" = 4723
OR "astrobin_image_software"."software_id" = 4723
OR "astrobin_image_filters"."filter_id" = 4723
OR "astrobin_image_imaging_cameras"."camera_id" = 4723
OR "astrobin_image_mounts"."mount_id" = 4723
OR "astrobin_image_focal_reducers"."focalreducer_id" = 4723
OR "astrobin_image_accessories"."accessory_id" = 4723
OR "astrobin_image_guiding_cameras"."camera_id" = 4723 )

So this query takes over 2 seconds... Can you suggest a way to improve this mess?

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
Salvatore Iovene
  • 2,064
  • 1
  • 17
  • 31
  • First off.. `Q(**{'%s__gear_ptr__pk' % t: gear.pk}) for t in types` can be `[Q('%s__gear_ptr__pk' % t = gear.pk) for t in types]` – karthikr Nov 06 '13 at 20:16
  • 1
    Since you are doing a simple `and`, you dont even need `Q` . Just do `", ".join(['%s__gear_ptr__pk = %s' % (t, gear.pk) for t in types])` – karthikr Nov 06 '13 at 20:18
  • I'm confused. Isn't this query returning an incorrect count anyway? To get the count you should be doing `select count(*) from (select 1 from tbl1 where id = ? union all select 1 from tbl2 where id = ? union all … ) as sub;` – Denis de Bernardy Nov 07 '13 at 07:22
  • Weird model structure, why not to have one base `Gear` model with `image_id` field? – alko Dec 06 '13 at 07:30

0 Answers0