Hi People. I need some help optimizing this query:
SELECT u.friendly_name, distance(transform(u.curr_location,32628), rc.agg_geometry) AS thedistance, u.mobile FROM (SELECT transform(collect(r.geometry),32628) AS agg_geometry, t.county FROM pt_madeira_roads r, pt_madeira_toponymy t WHERE r.name ILIKE '%salão são roque%' AND t.county='Funchal' AND r.geometry && t.geometry GROUP BY t.county) rc, units AS u WHERE expand(rc.agg_geometry,1000) && transform(u.curr_location,32628) AND u.customer_id='14' AND (u.even_code='A' OR u.even_code='B') ORDER BY thedistance LIMIT 10; Total query runtime: 1578 ms. 16 rows retrieved. this is the explain analyze output: "Limit (cost=93.92..93.93 rows=1 width=78)" " -> Sort (cost=93.92..93.93 rows=1 width=78)" " Sort Key: distance(transform(u.curr_location, 32628), rc.agg_geometry)" " -> Nested Loop (cost=85.59..93.91 rows=1 width=78)" " Join Filter: (expand(rc.agg_geometry, 1000::double precision) && transform(u.curr_location, 32628))" " -> HashAggregate (cost=85.59..85.60 rows=1 width=226)" " -> Nested Loop (cost=0.00..85.58 rows=1 width=226)" " -> Seq Scan on pt_madeira_toponymy t (cost=0.00..2.74 rows=10 width=15770)" " Filter: (county = 'Funchal'::text)" " -> Index Scan using pt_madeira_roads_idx on pt_madeira_roads r (cost=0.00..8.27 rows=1 width=213)" " Index Cond: (r.geometry && t.geometry)" " Filter: ((r.name ~~* '%salão são roque %'::text) AND (r.geometry && t.geometry))" " -> Index Scan using "units_customerID_idx" on units u (cost=0.00..8.28 rows=1 width=46)" " Index Cond: (customer_id = 14::bigint)" " Filter: (((even_code)::text = 'A'::text) OR ((even_code)::text = 'B'::text))" I still get Seq Scans although all used fields are indexed, hence the time used... :-( Although this is almost 'targeted' to the beautiful SQL head of Regina's any input would be extremely appreciated... ;-) Thank you! -- Pedro Doria Meunier Ips da Olaria Edf. Jardins do Garajau, 4 r/c Y 9125-163 Caniço Madeira Portugal GSM: +351 96 17 20 188 Skype: pdoriam http://www.madeiragps.com
signature.asc
Description: This is a digitally signed message part