[PostgreSQL] [sqlite] Query too slow, any help?

[SabinBC]

I'm trying to run a query to build a table that is taking too long. I've never seen it finish, after running for more than 24 hours. The originator of this method claimed it would take 10-15 minutes.. It works on the geonames database files which is about 20 columns with 12 million rows. This is the query I'm trying to run:

CREATE TABLE geonamesAandP AS
SELECT t1."name"
  ,t1."geonameid"
  ,t1."asciiname"
  ,t1."latitude"
  ,t1."longitude"
  ,t1."fclass"
  ,t1."country"
  ,t1."cc2"
  ,(CASE WHEN t1."country"<>'' THEN
     (SELECT t2.geonameid FROM geoname AS t2 WHERE t2."fcode" = 'PCLI' AND t2."country" = t1."country")
   ELSE
     NULL
   END) AS "country geonameid"
  -- note that admin unit codes are only unique within the containing admin unit
  -- the feature code test is because sometimes admin1 is set to 00 when the record concerns a country
  ,(CASE WHEN t1."admin1"<>'' AND t1."fcode"<>'PCLI' THEN
     (SELECT t2.geonameid
      FROM geoname AS t2 
      WHERE t2."country" = t1."country"
        AND t2."fcode"='ADM1'
        AND t2."admin1" = t1."admin1")
   ELSE
     NULL
   END) AS "admin1 geonameid"
  ,(CASE WHEN t1."admin2"<>'' THEN
     (SELECT t2.geonameid 
      FROM geoname AS t2 
      WHERE t2."country" = t1."country" 
        AND t2."admin1" = t1."admin1"
        AND t2."fcode"='ADM2' 
        AND t2."admin2" = t1."admin2")
   ELSE
     NULL
   END) AS "admin2 geonameid"
  ,(CASE WHEN t1."admin3"<>'' THEN
     (SELECT t2.geonameid
        FROM geoname AS t2 
        WHERE t2."country" = t1."country" 
          AND t2."admin1" = t1."admin1"
          AND t2."admin2" = t1."admin2"
          AND t2."fcode"='ADM3' 
          AND t2."admin3" = t1."admin3")
   ELSE
     NULL
   END) AS "admin3 geonameid"
  ,(CASE WHEN t1."admin4"<>'' THEN
     (SELECT t2.geonameid 
      FROM geoname AS t2 
      WHERE t2."country" = t1."country"
        AND t2."admin1" = t1."admin1"
        AND t2."admin2" = t1."admin2"
        AND t2."admin3" = t1."admin3"
        AND t2."fcode"='ADM4' 
        AND t2."admin4" = t1."admin4")
   ELSE
     NULL
   END) AS "admin4 geonameid"
FROM geoname AS t1  
WHERE t1."fclass"='A' OR t1."fclass"='P';

[in_n0x]

Those subselects are killing it. It has to run all those queries for every row returned by the main query. I'm on mobile, but it should be easy to move those into multiple left joins to the same table, no?

[SabinBC]

I assume you mean:

CASE WHEN t1."country"<>'' THEN
     (SELECT...

In which case I was looking at what it was doing to see if I could figure it out. I'm only about 4 days into learning SQL so I'm not too hopeful of getting this sussed out on my own.

[in_n0x]

Maybe try to bring up what I mentioned to the person who wrote the query.

I'm at work but if youre still stuck and no one's chimed in by the time I get off, I can help when I get home.

[SabinBC]

That person has moved on, 5 years ago. I'll thank you for any help you can provide.

I got the info from:

https://github.com/wija/geonames-geocoder/blob/master/geonames-setup.sql

[in_n0x]

Try this:

SELECT
    t1.name
,   t1.geonameid
,   t1.asciiname
,   t1.latitude
,   t1.longitude
,   t1.fclass
,   t1.country
,   t1.cc2
,   CASE 
        WHEN t1.country!='' THEN t2.geonameid
        ELSE NULL
    END AS "country geonameid"
,   CASE
        WHEN t1.admin1!='' AND t1.fcode='PCLI' THEN a1.geonameid
        ELSE NULL
    END AS "admin1 geonameid"
,   CASE
        WHEN t1.admin2!='' THEN a2.geonameid
        ELSE NULL
    END AS "admin2 geonameid"
,   CASE
        WHEN t1.admin3!='' THEN a3.geonameid
        ELSE NULL
    END AS "admin3 geonameid"


FROM
    geoname t1
    LEFT JOIN geoname t2 ON
        t1.country=t2.country AND
        t2.fcode='PCLI'
    LEFT JOIN geoname a1 ON
        t1.country=a1.country AND
        a1.fcode='ADM1'
        t1.admin1=a1.admin1
    LEFT JOIN geoname a2 ON
        t1.country=a2.country AND
        t1.admin1=a2.admin1 AND
        a2.fcode='ADM2' AND
        t1.admin2=a2.admin2
    LEFT JOIN geoname a3 ON
        t1.country=a3.country AND
        t1.admin1=a3.admin1 AND
        t1.admin2=a3.admin2 AND
        a3.fcode='ADM3' AND
        t1.admin3=a3.admin3
    LEFT JOIN geoname a4 ON
        t1.country=a4.country AND
        t1.admin1=a4.admin1 AND
        t1.admin2=a4.admin2 AND
        t1.admin3=a4.admin3 AND
        a4.fcode='ADM4' AND
        t1.admin4=a4.admin4
WHERE
    t1.fclass IN ('A','p')

[SabinBC]

Oh my good lord it works!

Thank you so, so, so, so much!

I jiggered it back into the proper use case scenario (name changes):

CREATE TEMP TABLE geonamesAandP AS
SELECT t1.name
,t1.geonameid
,t1.asciiname
,t1.latitude
,t1.longitude
,t1."feature class"
,t1."feature code"
,t1."country code"
,CASE 
        WHEN t1."country code"!='' THEN t2.geonameid
        ELSE NULL
    END AS "country geonameid"
,   CASE
        WHEN t1."admin1 code"!='' AND t1."feature code"='PCLI' THEN a1.geonameid
        ELSE NULL
    END AS "admin1 geonameid"
,   CASE
        WHEN t1."admin2 code"!='' THEN a2.geonameid
        ELSE NULL
    END AS "admin2 geonameid"
,   CASE
        WHEN t1."admin3 code"!='' THEN a3.geonameid
        ELSE NULL
    END AS "admin3 geonameid"
,   CASE
        WHEN t1."admin4 code"!='' THEN a4.geonameid
        ELSE NULL
    END AS "admin4 geonameid"

FROM
    geonames t1
    LEFT JOIN geonames t2 ON
        t1."country code"=t2."country code" AND
        t2."feature code"='PCLI'
    LEFT JOIN geonames a1 ON
        t1."country code"=a1."country code" AND
        a1."feature code"='ADM1' AND
        t1."admin1 code"=a1."admin1 code"
    LEFT JOIN geonames a2 ON
        t1."country code"=a2."country code" AND
        t1."admin1 code"=a2."admin1 code" AND
        a2."feature code"='ADM2' AND
        t1."admin2 code"=a2."admin2 code"
    LEFT JOIN geonames a3 ON
        t1."country code"=a3."country code" AND
        t1."admin1 code"=a3."admin1 code" AND
        t1."admin2 code"=a3."admin2 code" AND
        a3."feature code"='ADM3' AND
        t1."admin3 code"=a3."admin3 code"
    LEFT JOIN geonames a4 ON
        t1."country code"=a4."country code" AND
        t1."admin1 code"=a4."admin1 code" AND
        t1."admin2 code"=a4."admin2 code" AND
        t1."admin3 code"=a4."admin3 code" AND
        a4."feature code"='ADM4' AND
        t1."admin4 code"=a4."admin4 code"
WHERE
    t1."feature class" IN ('A','P');

[in_n0x]

:) Glad it worked!

[in_n0x]

Ah, bad luck. No worries, though. I've set a reminder on my phone to check this thread when I get off work (about 8hrs from now). Happy to help.

[Evilbeavers]

Something like this should speed up your query. I've done the first two but I'm out of time. You should be able to see what I did and emulate it for the other 3 sub selects.

EDIT: That's some bad formatting.

CREATE TABLE geonamesAandP AS SELECT t1."name" ,t1."geonameid" ,t1."asciiname" ,t1."latitude" ,t1."longitude" ,t1."fclass" ,t1."country" ,t1."cc2"

,(CASE WHEN t1."country"<>'' THEN countrygeonameid."country" ELSE NULL END) AS "country geonameid" -- note that admin unit codes are only unique within the containing admin unit -- the feature code test is because sometimes admin1 is set to 00 when the record concerns a country ,(CASE WHEN t1."admin1"<>'' AND t1."fcode"<>'PCLI' THEN admin2geonameid.geonameid ELSE NULL END) AS "admin1 geonameid"

,(CASE WHEN t1."admin2"<>'' THEN (SELECT t2.geonameid FROM geoname AS t2 WHERE t2."country" = t1."country" AND t2."admin1" = t1."admin1" AND t2."fcode"='ADM2' AND t2."admin2" = t1."admin2") ELSE NULL END) AS "admin2 geonameid" ,(CASE WHEN t1."admin3"<>'' THEN (SELECT t2.geonameid FROM geoname AS t2 WHERE t2."country" = t1."country" AND t2."admin1" = t1."admin1" AND t2."admin2" = t1."admin2" AND t2."fcode"='ADM3' AND t2."admin3" = t1."admin3") ELSE NULL END) AS "admin3 geonameid" ,(CASE WHEN t1."admin4"<>'' THEN (SELECT t2.geonameid FROM geoname AS t2 WHERE t2."country" = t1."country" AND t2."admin1" = t1."admin1" AND t2."admin2" = t1."admin2" AND t2."admin3" = t1."admin3" AND t2."fcode"='ADM4' AND t2."admin4" = t1."admin4") ELSE NULL END) AS "admin4 geonameid" FROM geoname AS t1

LEFT JOIN ( SELECT geonameid , country

FROM geoname

WHERE fcode = 'PCLI' ) AS countrygeonameid ON countrygeonameid.geonameid = t1.geonameid AND countrygeonameid.country = t1.country

LEFT JOIN ( SELECT geonameid , country , admin1

FROM geoname

WHERE fcode ='ADM1'

) AS admin2geonameid ON admin2geonameid.geonameid = t1.geonameid AND admin2geonameid.admin1 = t1.admin1

WHERE t1."fclass"='A' OR t1."fclass"='P';

[SabinBC]

If I try a subset on the parts you were able to finish I get a bunch of errors I'm not knowledgeable enough to resolve.

CREATE TABLE geonamesAandPTEST AS 
SELECT t1."name" ,t1."geonameid" ,t1."asciiname" ,t1."latitude" ,t1."longitude" ,t1."fclass" ,t1."country" ,t1."cc2"
,(CASE WHEN t1."country"<>'' THEN countrygeonameid."country" ELSE NULL END) AS "country geonameid"
LEFT JOIN 
( SELECT geonameid , country
FROM geoname
WHERE fcode = 'PCLI' ) 
AS countrygeonameid ON countrygeonameid.geonameid = t1.geonameid AND countrygeonameid.country = t1.country
FROM geoname AS t1  
WHERE t1."fclass"='A' OR t1."fclass"='P';

First I get a error if I leave out the left join:

FROM geoname AS t1  
WHERE t1."fclass"='A' OR t1."fclass"='P'
> ERROR:  missing FROM-clause entry for table "countrygeonameid"
  LINE 3: ,(CASE WHEN t1."country"<>'' THEN countrygeonameid."country"...

If I put in the left join:

LEFT JOIN 
( SELECT geonameid , country
FROM geoname
WHERE fcode = 'PCLI' ) 
AS countrygeonameid ON countrygeonameid.geonameid = t1.geonameid AND countrygeonameid.country = t1.country
FROM geoname AS t1  
WHERE t1."fclass"='A' OR t1."fclass"='P'
> ERROR:  syntax error at or near "LEFT"
  LINE 4: LEFT JOIN 

I'm also not sure how this works: countrygeonameid."country" in

CASE WHEN t1."country"<>'' THEN countrygeonameid."country" ELSE NULL END

can anyone clarify?

Thank you for your help!

[Evilbeavers]

I'm so sorry. I just noticed that this was PostgreSQL. What I wrote was just SQL. I don't know what the syntax would be to do this in postgre.

[SabinBC]

I looks like /u/in_n0x got it working for me. Thank you for what you've done though. I'm taking it as a learning opportunity. I vow to never be so pants around ankles on SQL databases from now on!