Hi 2018-01-16 18:57 GMT+01:00 hmidi slim <hmidi.sl...@gmail.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.steh...@gmail.com>: > >> Hi >> >> 2018-01-16 17:44 GMT+01:00 hmidi slim <hmidi.sl...@gmail.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.mo...@avbrief.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. >>>> >>> >>> >> >
