Do you have any indecies? https://www.postgresql.org/docs/current/static/indexes-expressional.html might be helpful to you.
Also, EXPLAIN will help you understand how your query is being run and where it can be improved. https://www.postgresql.org/docs/current/static/using-explain.html http://postgresguide.com/performance/explain.html http://jimkeener.com/posts/explain-pg Jim On Tue, Jan 16, 2018 at 11:32 AM, hmidi slim <hmidi.sl...@gmail.com> wrote: > Hi, > 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. > I wrote this query: > select g.name, t.city > from geoname as g, test_table as t > 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? >
