OPtimize the performance of a query

От

hmidi slim

Тема

Дата

16 января 2018 г. в 19:32:47

Msg-id

CAMsqVxsNsGJETtBZG=eLgFahGyv4zXaQdgGE0KfgBM+_5pWDww@mail.gmail.com

обсуждение

Список

pgsql-general

Дерево обсуждения

OPtimize the performance of a query hmidi slim <hmidi.slim2@gmail.com> 16 января 2018 г. в 19:32:47

Re: OPtimize the performance of a query Alban Hertroys <haramrae@gmail.com> 16 января 2018 г. в 19:49:30

Re: OPtimize the performance of a query James Keener <jim@jimkeener.com> 16 января 2018 г. в 19:46:50

Re: OPtimize the performance of a query Melvin Davidson <melvin6925@gmail.com> 16 января 2018 г. в 19:48:40

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?

В списке pgsql-general по дате отправления

Предыдущее

От: Scott Marlowe

Дата: 16 января 2018 г. в 19:01:52

Сообщение: Re: SSD filesystem aligned to DBMS

Следующее

От: hmidi slim

Дата: 16 января 2018 г. в 19:44:29

Сообщение: Re: OPtimize the performance of a query