Re: OPtimize the performance of a query

On Tue, Jan 16, 2018 at 11:46 AM, James Keener <jim(at)jimkeener(dot)com> wrote:

> Do you have any indecies? https://www.postgresql.org/
> docs/current/static/indexes-expressional.html might be helpful to you.
>
> Also, EXPLAIN will help you understand how your query is being run and
> where it can be improved.
>
> https://www.postgresql.org/docs/current/static/using-explain.html
> http://postgresguide.com/performance/explain.html
> http://jimkeener.com/posts/explain-pg
>
> Jim
>
> On Tue, Jan 16, 2018 at 11:32 AM, hmidi slim <hmidi(dot)slim2(at)gmail(dot)com>
> wrote:
>
>> 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?
>>
>
>
It would have been more helpful if you had included
the actual table structures for both tables.
However, I would start by creating separate indexes on
lower(feature_class)
lower(country_code)
lower(admin1)
lower(name)
lower(city)

That being said, you are better off forcing lowercase on all fields
BEFORE inserting into the table.
EG:

INSERT INTO test_table VALUES (lower(some_key), lower(name),
lower(feature_class), ....)

Then you would would not need to use lower() in the indexes or the query.

Please, in the future, always include your version of PostgreSQL and O/S

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.