0

We've got some slow running queries in our DB and I think some of them can be solved by changing the underlying SQL.

Given the following:

Model.objects.filter(rel_id__in=[1,2,3])

Outputs (abridged):

SELECT all, the, fields FROM "model" WHERE ("rel_id" IN (1, 2, 2))

What I want is for the SQL to change to:

SELECT all, the, fields FROM "model" WHERE ("rel_id" = ANY(array[1, 2, 2]))

I know that this might be not be optimum approach, but EXPLAIN ANALYSE returns significantly lower figures with the second query.

So my question is, how can I get the second SQL query from my QuerySet lookup?

Thanks in advance

justcompile
  • 3,362
  • 1
  • 29
  • 37
  • That's weird. Please show both query plans. Usually those two would be equivalent - `IN` gets turned into `= ANY` quite early in planning IIRC. – Craig Ringer Jul 25 '14 at 12:48

1 Answers1

1

Could possibly be done like this:

Model.objects.extra(
    where=['"rel_id" = ANY(array%s)' % (str(your_list))]
)

otherwise you probably have to resort to using raw.

rinti
  • 1,273
  • 1
  • 12
  • 16
  • 1
    It would be better to mention that `your_list` should be a sequence of strings to use `str.join`. – falsetru Jul 25 '14 at 12:32
  • There is a caveat to this whereby your_list is a queryset or valueslist which django truncates. It should be changed to: '"feed_id" = ANY(array[%s])' % ','.join([str(ident] for ident in your_list) – justcompile Jul 28 '14 at 14:05