pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedExplain Analyze - Total runtime very differentes
2+ messages / 2 participants
[nested] [flat]
* Explain Analyze - Total runtime very differentes
@ 2008-10-18 19:14 tarcizioab@c3sl.ufpr.br
0 siblings, 1 reply; 2+ messages in thread
From: tarcizioab@c3sl.ufpr.br @ 2008-10-18 19:14 UTC (permalink / raw)
To: pgsql-performance
Hello friends ...
I'm evaluating the performance of algorithms for optimization of queries.
I am comparing results between the algorithm of Dynamic Programming and an
implementation of Kruskal's algorithm. When submitting a query that makes
reference to only 2 tables of my base, logically the same "Query Plan" is
shown. But the "Total runtime" displayed by the command "Explain-Analyze"
presents a variation of time very high:
Dynamic Programming Total runtime: 1204.220 ms
Kruskal Total runtime: 3744.879 ms
No change of data (insert, delete, update) in the tables was made during
the tests. The same query was submitted several times (with Kruskal and
Dynamic Programming algorithms) and the variation of results persists.
The "explain analyze" only reports the time to run *execute* the query.
With the same "Query Plan", does not understand why this variation occurs.
In annex the Query Plans
If someone can help me.
Thank's for attention.
Tarcizio Bini
Attachments:
QueryPlans.txt (text/plain, 2.6K) download
(Kruskal) QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=474090.39..474090.40 rows=1 width=4) (actual time=3744.711..3744.712 rows=1 loops=1)
-> Nested Loop (cost=0.00..474086.63 rows=1501 width=4) (actual time=33.844..3744.296 rows=573 loops=1)
Join Filter: (public.lineitem.l_quantity < (subplan))
-> Seq Scan on part (cost=0.00..7063.92 rows=200 width=4) (actual time=0.245..69.567 rows=211 loops=1)
Filter: ((p_brand = 'Brand#12'::bpchar) AND (p_container = 'LG CAN'::bpchar))
-> Index Scan using i_l_partkey on lineitem (cost=0.00..95.35 rows=23 width=12) (actual time=12.459..12.482 rows=30 loops=211)
Index Cond: (public.lineitem.l_partkey = part.p_partkey)
SubPlan
-> Aggregate (cost=97.35..97.37 rows=1 width=4) (actual time=0.163..0.163 rows=1 loops=6334)
-> Index Scan using i_l_partkey on lineitem (cost=0.00..97.29 rows=23 width=4) (actual time=0.004..0.151 rows=31 loops=6334)
Index Cond: (l_partkey = $0)
Total runtime: 3744.879 ms
(12 rows)
Time: 3778,490 ms
Timing is on.
(Dynamic Programming) QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=474090.39..474090.40 rows=1 width=4) (actual time=1204.064..1204.064 rows=1 loops=1)
-> Nested Loop (cost=0.00..474086.63 rows=1501 width=4) (actual time=11.754..1203.669 rows=573 loops=1)
Join Filter: (public.lineitem.l_quantity < (subplan))
-> Seq Scan on part (cost=0.00..7063.92 rows=200 width=4) (actual time=0.264..67.012 rows=211 loops=1)
Filter: ((p_brand = 'Brand#12'::bpchar) AND (p_container = 'LG CAN'::bpchar))
-> Index Scan using i_l_partkey on lineitem (cost=0.00..95.35 rows=23 width=12) (actual time=3.841..3.864 rows=30 loops=211)
Index Cond: (public.lineitem.l_partkey = part.p_partkey)
SubPlan
-> Aggregate (cost=97.35..97.37 rows=1 width=4) (actual time=0.049..0.049 rows=1 loops=6334)
-> Index Scan using i_l_partkey on lineitem (cost=0.00..97.29 rows=23 width=4) (actual time=0.004..0.037 rows=31 loops=6334)
Index Cond: (l_partkey = $0)
Total runtime: 1204.220 ms
(12 rows)
Time: 1208,423 ms
Timing is on.
* Re: Explain Analyze - Total runtime very differentes
@ 2008-10-19 08:55 Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
parent: tarcizioab@c3sl.ufpr.br
0 siblings, 0 replies; 2+ messages in thread
From: Stefan Kaltenbrunner @ 2008-10-19 08:55 UTC (permalink / raw)
To: tarcizioab@c3sl.ufpr.br; +Cc: pgsql-performance
tarcizioab@c3sl.ufpr.br wrote:
> Hello friends ...
>
> I'm evaluating the performance of algorithms for optimization of queries.
> I am comparing results between the algorithm of Dynamic Programming and an
> implementation of Kruskal's algorithm. When submitting a query that makes
> reference to only 2 tables of my base, logically the same "Query Plan" is
> shown. But the "Total runtime" displayed by the command "Explain-Analyze"
> presents a variation of time very high:
>
> Dynamic Programming Total runtime: 1204.220 ms
>
> Kruskal Total runtime: 3744.879 ms
>
> No change of data (insert, delete, update) in the tables was made during
> the tests. The same query was submitted several times (with Kruskal and
> Dynamic Programming algorithms) and the variation of results persists.
>
> The "explain analyze" only reports the time to run *execute* the query.
> With the same "Query Plan", does not understand why this variation occurs.
>
> In annex the Query Plans
sure it it not something as simple as a caching effect - ie you run the
slow variant first and pg and/or the OS buffered data and the repeated
execution just got a benefit from that ?
Try running all variations a few dozend times both in cached and
uncached state and you should see the difference getting leveled out.
Stefan
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-10-19 08:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-10-18 19:14 Explain Analyze - Total runtime very differentes tarcizioab@c3sl.ufpr.br
2008-10-19 08:55 ` Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox