[Answered ]-Query to retrieve neighbors is too slow

by

1👍

The query plans for those two queries are radically different. The first (slower) one isn’t hitting any indexes, and is doing two left joins, both of which result in way, way more rows being processed and returned. From what I interpret of the intention of the Django ORM syntax, it doesn’t sound like you would truly want to do left joins here.

I would recommend considering dropping down into raw SQL in this case from within the Django ORM, and hybridize the two. e.g. if you take the first one, and transform it to something like this:

SELECT DISTINCT "app_vertex"."id"
FROM "app_vertex"
JOIN "app_edge" ON ("app_vertex"."id" = "app_edge"."orig_id")
JOIN "app_edge" T4 ON ("app_vertex"."id" = T4."dest_id")
WHERE ("app_edge"."dest_id" = 1061
       OR T4."orig_id" = 1061);

Two questions there: How does that version perform, and does it give you the results you’re looking for?

For more info on raw queries, check out this section of the Django doc.

Response to comment from OP:

The query plan for the query I suggested also shows that it’s not hitting any indexes.

Do you have indexes on both tables for the columns involved? I suspect not, especially since for this specific query, we’re looking for a single value, which means if there were indexes, I would be very surprised if the query planner determined a sequential scan were a better choice (OTOH, if you were looking for a wide range of rows, say, over 10% of the rows in the tables, the query planner might correctly make such a decision).

👤khampson

1👍

I propose another query could be:

# Get edges which contain Vertex v, "only" optimizes fields returned
edges = Edge.objects.filter(Q(orig=v) | Q(dest=v)).only('orig_id', 'dest_id')
# Get set of vertex id's to discard duplicates
vertex_ids = {*edges.values_list('orig_id', flat=True), *edges_values_list('dest_id', flat=True)}
# Get list of vertices, excluding the original vertex
vertices = Vertex.objects.filter(pk__in=vertex_ids).exclude(pk=v.pk)

This shouldn’t require any joins and shouldn’t suffer from the race conditions you mention.

👤A. J. Parr

Leave a comment