babuno5
asked on
PostgreSQL Spatial Query Optimization
i have a table called road
It has
select count(*) from road;
count
---------
1098822
I want to fire this query in the database
select name, astext(the_geom) from road where lower(city) like '%mumbai%' and lower(name) like '%carter%';
explain analyze select name, astext(the_geom) from road where lower(city) like '%mumbai%' and lower(name) like '%carter%';
QUERY PLAN
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Seq Scan on road (cost=0.00..45164.62 rows=1 width=478) (actual time=282.400..1299.810 rows=16 loops=1)
Filter: ((lower((city)::text) ~~ '%mumbai%'::text) AND (lower((name)::text) ~~ '%carter%'::text))
Total runtime: 1299.864 ms
(3 rows)
I would like suggestions from experts as to a way to reduce the time for execution or may be optimize the same for better performance
It has
select count(*) from road;
count
---------
1098822
I want to fire this query in the database
select name, astext(the_geom) from road where lower(city) like '%mumbai%' and lower(name) like '%carter%';
explain analyze select name, astext(the_geom) from road where lower(city) like '%mumbai%' and lower(name) like '%carter%';
QUERY PLAN
--------------------------
Seq Scan on road (cost=0.00..45164.62 rows=1 width=478) (actual time=282.400..1299.810 rows=16 loops=1)
Filter: ((lower((city)::text) ~~ '%mumbai%'::text) AND (lower((name)::text) ~~ '%carter%'::text))
Total runtime: 1299.864 ms
(3 rows)
I would like suggestions from experts as to a way to reduce the time for execution or may be optimize the same for better performance
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER