explain vs. explain analyze

Поиск
Список
Период
Сортировка
От Elein
Тема explain vs. explain analyze
Дата
Msg-id 3C8FC060.1060406@nextbus.com
обсуждение исходный текст
Список pgsql-general
I am getting widely disparate results from the explain and actual
results when using explain analyze.

7.2. Yes I'm vacuum analyzing every night.

What kinds of things could cause this disparity?  This query
has lots of date range qualifications and wild subqueries.  Could the
complexity affect this? (Besides opening it up to errors :-\)
How about function calls?

I have experimented with the query in a number of different ways,
changing join quals from "on" to the where clause, removing trivial
subqueries.  I can get slightly different costs and actuals, but nothing
seems to
affect the disparity between the two.

Ideas?

NOTICE:  QUERY PLAN:

Sort  (cost=2566.14..2566.14 rows=1 width=186) (actual
time=29512.63..29512.63 rows=8 loops=1)
   ->  Aggregate  (cost=2566.11..2566.13 rows=1 width=186) (actual
time=29445.32..29512.38 rows=8 loops=1)
         ->  Group  (cost=2566.11..2566.13 rows=1 width=186) (actual
time=29433.49..29505.81 rows=5333 loops=1)
               ->  Sort  (cost=2566.11..2566.11 rows=1 width=186)
(actual time=29433.46..29436.94 rows=5333 loops=1)
                     ->  Nested Loop  (cost=1634.10..2566.10 rows=1
width=186) (actual time=2225.72..29254.92 rows=5333 loops=1)
                           ->  Nested Loop  (cost=1634.10..1921.61
rows=1 width=147) (actual time=2200.57..2519.16 rows=8 loops=1)
                                 ->  Subquery Scan j
(cost=1634.10..1640.82 rows=45 width=59) (actual time=2113.72..2120.38
rows=8 loops=1)
                                       ->  Aggregate
(cost=1634.10..1640.82 rows=45 width=59) (actual time=2113.70..2120.28
rows=8 loops=1)
                                             ->  Group
(cost=1634.10..1639.70 rows=448 width=59) (actual time=2112.53..2119.05
rows=802 loops=1)
                                                   ->  Sort
(cost=1634.10..1634.10 rows=448 width=59) (actual time=2112.51..2113.06
rows=802 loops=1)
                                                         ->  Hash Join
(cost=5.81..1614.35 rows=448 width=59) (actual time=1784.17..2102.68
rows=802 loops=1)
                                                               ->  Seq
Scan on jobsequences js  (cost=0.00..912.45 rows=11452 width=27) (actual
time=14.36..640.19 rows=12300 loops=1)
                                                               ->  Hash
  (cost=5.79..5.79 rows=8 width=32) (actual time=1415.19..1415.19 rows=0
loops=1)
                                                                     ->
  Seq Scan on jobs j  (cost=0.00..5.79 rows=8 width=32) (actual
time=1255.60..1415.13 rows=8 loops=1)
                                 ->  Index Scan using jobsequences_pkey
on jobsequences js  (cost=0.00..5.97 rows=1 width=35) (actual
time=14.39..14.40 rows=1 loops=8)
                           ->  Index Scan using pos_timeidx on positions
x  (cost=0.00..383.82 rows=10427 width=39) (actual time=0.90..110.68
rows=11894 loops=8)
                           SubPlan
                             ->  Result  (cost=0.00..0.01 rows=1
width=0) (actual time=2.38..2.38 rows=1 loops=5333)
Total runtime: 29516.11 msec

EXPLAIN

--
--------------------------------------------------------
elein@nextbus.com
(510)420-3120
www.nextbus.com
    rose is a rose is a rose is a rose --gertrude stein
--------------------------------------------------------


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

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: checkpoint
Следующее
От: Jan Wieck
Дата:
Сообщение: Re: more about pg_toast growth