explain vs. explain analyze

Поиск
Список
Период
Сортировка
Искать
От
Elein
Тема
explain vs. explain analyze
Дата
Msg-id
3C8FC060.1060406@nextbus.com
Список

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
FAQ