explain vs. explain analyze - Mailing list pgsql-general
From | Elein |
---|---|
Subject | explain vs. explain analyze |
Date | |
Msg-id | 3C8FC060.1060406@nextbus.com Whole thread Raw |
List | 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 by date: