CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2postgresql-9.5
Mahi
Mahi

Reputation: 21913

Multiple sub queries for each result, how to make faster?

I'm doing a query like this:

cursor.execute(
    '''
    SELECT person.id, person.name
    FROM person
    JOIN vehicle ON vehicle.owner_id = person.id
    WHERE person.age >= 18 AND vehicle.model = %s
    ''',
    ('Toyota Auris',)
)
people = [dict(row) for row in cursor.fetchall()]

And then for every person, I fetch their vehicles' informations:

for person in people:
    cursor.execute(
        '''
        SELECT vehicle.id AS id, vehicle.name AS name, vehicled.model AS model
        FROM vehicle
        JOIN person ON person.id = vehicle.owner_id
        WHERE person.id = %s
        ''',
        (person['id'],)
    )
    person['vehicles'] = [dict(row) for row in cursor.fetchall()]

But this vehicle fetching is extremely slow, as I have more than 1 million people and 100,000 vehicles registered. Is there any way to make the second query faster?

So what I want is all person objects who own a vehicle matching to a particular model, and all of their vehicles, regardless of the vehicle's model.

Upvotes: 1

Views: 84

Answers (2)

鄭有維
鄭有維

Reputation: 265

try :

 cursor.execute(
    '''
    SELECT person.id, person.name
    FROM person
    WHERE person.age > 17
    '''
)
people = [dict(row) for row in cursor.fetchall()]

and

 for person in people:
    cursor.execute(
        '''
        SELECT vehicle.id AS id, vehicle.name AS name, vehicled.model AS model
        FROM vehicle
        WHERE vehicle.owner_id= %s and vehicle.model = %s
        ''',
        (person['id'],'Toyota Auris',)
    )
    person['vehicles'] = [dict(row) for row in cursor.fetchall()]

Upvotes: 0

Anis
Anis

Reputation: 3094

You could try fetching first the people having a Toyota Auris then doing a double join on the vehicles. All of this fits in one query. Something along the lines of

SELECT v1.id, person.id AS pid, v2.id AS vid v2.model AS model FROM vehicle AS v1 JOIN person ON v1.owner_id = person.id JOIN vehicle AS v2 ON v2.owner_id = person.id WHERE person.age >= 18 AND v1.model = 'Toyota Auris';

Upvotes: 2

Related Questions