Sorry I forget the lower command when I wrote the code, it is like this: lower(g.country_code) like lower('US') (lower(g.feature_class) like lowwer('P') or lower(g.feature_class) like lower('L'))
please, don't do top post.
Your query must be slow. When you use LIKE instead =. It is terrible performance issue.
So don't use "LIKE" is first rule. Second - you can create functional indexes
>I have two tables in the same database: geoname and test_table.
>The geoname table contains many columns which are: name, feature_class, feature_code, admin1, admin2,admin3, name and so on.
>The second table 'test_table' contains only the columns: city, state.
>There is no join between the two tables and I want to make a match between the data contained in each of them because I need the result for a farther process.
>where (lower(g.feature_class) like 'P' or lower(g.feature_class) like 'L')
>and lower(g.country_code) like 'US'
>and lower(g.admin1) like lower(t.state)
>and (lower(g.name) like lower(t.city) or lower(g.name) like lower(t.city || 'city'))
>The table geoname contains 370260 rows and the table test_table contains 10270 rows.
>The query took a long time to accomplish more than half an hour.Should I add another column in the table test_table which contains the country_code and make an inner join with the geoname table or >should I use indexs to accelerate the process?
Indexes are your friends ☺
I’d certainly add indexes on lower(g.feature_class, g.country_code) and lower(t.state)
Note “and lower(g.country_code) like 'US'” will not return any results as you’ve got the ‘US’ in UPPER…. Same as (lower(g.feature_class) like 'P' or lower(g.feature_class) like 'L')
Why are you using LIKE? Equals (=) is surely correct and probably faster?
