From: | James Keener <jim(at)jimkeener(dot)com> |
---|---|
To: | hmidi slim <hmidi(dot)slim2(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: OPtimize the performance of a query |
Date: | 2018-01-16 16:46:50 |
Message-ID: | CAG8g3tyy2i=U7N8KoY-iUQsokyAaXN__gSZ55e-4MPuMnAjMrw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
>
From | Date | Subject | |
---|---|---|---|
Next Message | Melvin Davidson | 2018-01-16 16:48:40 | Re: OPtimize the performance of a query |
Previous Message | hmidi slim | 2018-01-16 16:44:29 | Re: OPtimize the performance of a query |