OPtimize the performance of a query

From: hmidi slim <hmidi(dot)slim2(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: OPtimize the performance of a query
Date: 2018-01-16 16:32:47
Message-ID: CAMsqVxsNsGJETtBZG=eLgFahGyv4zXaQdgGE0KfgBM+_5pWDww@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hmidi slim 2018-01-16 16:44:29 Re: OPtimize the performance of a query
Previous Message Scott Marlowe 2018-01-16 16:01:52 Re: SSD filesystem aligned to DBMS