Re: OPtimize the performance of a query

Hi

2018-01-16 17:44 GMT+01:00 hmidi slim <hmidi(dot)slim2(at)gmail(dot)com>:

> 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

CREATE INDEX ON geoname ((lower(name)))

Regards

Pavel

>
>
> 2018-01-16 17:40 GMT+01:00 Martin Moore <martin(dot)moore(at)avbrief(dot)com>:
>
>>
>>
>> >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?
>>
>>
>>
>> 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?
>>
>>
>>
>>
>>
>> Martin.
>>
>
>