Optimizing nested loops in the query plan

Поиск
Список
Период
Сортировка
От Fran Fabrizio
Тема Optimizing nested loops in the query plan
Дата
Msg-id 3C61AFFB.90906@mmrd.com
обсуждение исходный текст
Ответы Re: Optimizing nested loops in the query plan
Список pgsql-general
Hello,

This is a query I'm working on at the moment.  It's taking about 4
seconds to run, and I see that most of the cost is on the Nested Loop
area of the explain output.  Is there a better way to write this query
to improve execution time, the tables aren't that large and I feel like
there's a good chance I can get it to run faster.   I'm not too familiar
with which part of the query is causing the nested loop and how to
optimize that sort of thing.  findregion() is a function I wrote which
executes very quickly.  'select findregion(entity_id) from
current_status' returns in subsecond time so I don't think this is the
problem.  Thanks for any tips you can give me.

explain select findregion(cs.entity_id) as region, r.name, cs.status,
count(*) from current_status cs, region r where r.region_id =
findregion(cs.entity_id) group by region, r.name, cs.status;
NOTICE:  QUERY PLAN:

Aggregate  (cost=440.47..455.46 rows=150 width=24)
  ->  Group  (cost=440.47..451.71 rows=1499 width=24)
        ->  Sort  (cost=440.47..440.47 rows=1499 width=24)
              ->  Nested Loop  (cost=0.00..361.40 rows=1499 width=24)
                    ->  Seq Scan on region  (cost=0.00..1.07 rows=7
width=16)
                    ->  Seq Scan on current_status  (cost=0.00..28.99
rows=1499 width=8)

EXPLAIN

Thanks,
Fran


В списке pgsql-general по дате отправления:

Предыдущее
От: "Steve Wolfe"
Дата:
Сообщение: Re: Dream Server?
Следующее
От: "Steve Wolfe"
Дата:
Сообщение: Request for Benchmarks...