CODEWITHSUNDEEP
postgresql
Andrei Vreja
Andrei Vreja

Reputation: 13

Query extremely slow because of sub-queries

I am trying to to port some data to a new structure but it's taking me forever.

The query is the following:

INSERT INTO atemp_addresses (id, city, state, country_id)
SELECT id, (SELECT name from cities WHERE id=adr.city_id limit 1),
(SELECT name FROM states WHERE id=(SELECT state_id FROM cities WHERE id=adr.city_id limit 1) limit 1), (SELECT country_id FROM states WHERE id=(SELECT state_id FROM cities WHERE id=adr.city_id limit 1) limit 1), FROM addresses adr

Because the data is held in other tables, I have to use all those subqueries, which slows it down a lot. Is there a faster way to do this?

Upvotes: 1

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is your query:

INSERT INTO atemp_addresses (id, city, state, country_id)
    SELECT id,
           (SELECT name from cities WHERE id = adr.city_id limit 1),
           (SELECT name FROM states WHERE id = (SELECT state_id FROM cities WHERE id=adr.city_id limit 1) limit 1),
           (SELECT country_id FROM states WHERE id=(SELECT state_id FROM cities WHERE id=adr.city_id limit 1) limit 1)
    FROM addresses adr;

Repeatedly, you are using LIMIT without an ORDER BY. This means that you are going to get an arbitrary matching row that can change from one invocation to the next.

The equivalent query should be:

INSERT INTO atemp_addresses (id, city, state, country_id)
    SELECT adr.id, c.name, s.name, s.country_id
    FROM addresses adr JOIN
         cities c
         ON adr.city_id = c.id JOIN
         states s
         ON c.state_id = s.id;

Your use of LIMIT suggests that there might be more than one city with the same id (a sign of poor database design?). If so:

INSERT INTO atemp_addresses (id, city, state, country_id)
    SELECT DISTINCT ON (adr.id) adr.id, c.name, s.name, s.country_id
    FROM addresses adr JOIN
         cities c
         ON adr.city_id = c.id JOIN
         states s
         ON c.state_id = s.id 
    ORDER BY adr.id;

Upvotes: 2

Related Questions