"Mysterious" - Dynamic Programming X GEQO - Mailing list pgsql-performance

From tarcizioab@c3sl.ufpr.br
Subject "Mysterious" - Dynamic Programming X GEQO
Date
Msg-id 55663.200.17.202.126.1224603619.squirrel@webmail.inf.ufpr.br
Whole thread Raw
List pgsql-performance
Hi!!!

I'm evaluating the performance of algorithms for optimization of queries.
I am comparing results between the algorithm of Dynamic Programming and
GEQO algorithm.

I use the base and queries provided by the benchmark OSDL DBT3 with scale
factor = 1. The queries were submitted randomly, I did the cleaning of
memory cache between tests (drop_caches) and restarting the database
postgres.

Stay surprise with the running time of the querie 11 of DBT3. These
results are an average of the "Total Runtime" after 20 executions:

Dynamic Programming Total runtime: 83.122,003 ms
Run-time optimizer algorithm  (Dynamic Programming): 0,453 ms

GEQO Total runtime: 18.266,819 ms
Run-time optimizer algorithm (GEQO): 0,440 ms

The GEQO optimizer Run-time is a little faster, not affecting the final
result. In theory the dynamic programming algorithm to choose the best
plan, resulting in less time to run the query.

Who knows what is happening?

In annex the Query Plans and Query 11 of DBT3.

Thank's for attention.

Tarcizio Bini.

Attachment

pgsql-performance by date:

Previous
From: Stefan Kaltenbrunner
Date:
Subject: Re: Explain Analyze - Total runtime very differentes
Next
From: "Lionel"
Date:
Subject: Hardware HD choice...