Re: OPtimize the performance of a query

Hi

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

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

https://en.wikipedia.org/wiki/Posting_style#Top-posting .. please, don't do
it.

IS DISTINCT FROM has sense if your data - or your queries has NULL. If not,
and it is probably your case, then <> should be preferred.

Regards

Pavel

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