Re: OPtimize the performance of a query

[hmidi slim <hmidi.slim2@xxxxxxxxx>]

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.stehule@xxxxxxxxx>:

Hi

2018-01-16 17:44 GMT+01:00 hmidi slim <hmidi.slim2@xxxxxxxxx>:

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.moore@xxxxxxxxxxx>:

 

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