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),
" -> 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
" -> 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
GSM: +351 96 17 20 188 Skype: pdoriam

Search Discussions

  • Martijn van Oosterhout at Nov 29, 2007 at 9:58 pm

    On Thu, Nov 29, 2007 at 01:26:00PM +0000, Pedro Doria Meunier wrote:
    Hi People.

    I need some help optimizing this query: <snip>
    I still get Seq Scans although all used fields are indexed, hence the
    time used... :-(
    A seq scan on a table with 10 rows is *good*. An index would take
    longer. What you really need is to send the output of EXPLAIN ANALYZE
    so we can see where the time actually went.

    Have a nice day,
    Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
    Those who make peaceful revolution impossible will make violent revolution inevitable.
    -- John F Kennedy

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
postedNov 29, '07 at 1:26p
activeNov 29, '07 at 9:58p



site design / logo © 2022 Grokbase