Re: OPtimize the performance of a query

I changed the operator like and I'm using the operator = .I got the results
much faster but I still have another question about operator. For
difference should I use '<>' or 'is distinct from' with indexes?

2018-01-16 17:49 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

> 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.
>>>
>>
>>
>