Thread: optimizer's cost formulas
Hello everybody!
I'm trying to understand the the query planner's cost estimator.
I was not able to find anywhere the formulas that estimate the cost
of each operation that the optimizer produces.
I only found this for the sequential scan:
SEQ SCAN = ( cpu_tuple_cost * rows ) + ( number of pages * seq_page_cost )
where :
cpu_tuple_cost=0.01
seq_page_cost=1.0
and "rows" and "number of pages" are given in the query plan (via EXPLAIN ANALYZE)
where :
cpu_tuple_cost=0.01
seq_page_cost=1.0
and "rows" and "number of pages" are given in the query plan (via EXPLAIN ANALYZE)
I am wondering where could I find the rest formulas for the rest operations
(e.g. HashAggregate, Nested Loop, Hash Join, Index Scan, Sort, etc)
I also looked at costsize.c but could not find a formula like the above or maybe I
couldn't make sense of it
I also looked at costsize.c but could not find a formula like the above or maybe I
couldn't make sense of it
Thank you in advance!
Hi, AFAIK there is no such thing in the code or documentation. Nevertheless, for a paper I've published last year, I tried to create mathematical transcriptions at least for the I/O costs. http://dl.acm.org/citation.cfm?id=2236584.2236588&coll=DL&dl=ACM&CFID=309621631&CFTOKEN=77712718 Have a look at the appendix. Regards, Daniel Bausch Am 06.04.2013 12:51, schrieb dafNi: > Hello everybody! > > I'm trying to understand the the query planner's cost estimator. > I was not able to find anywhere the formulas that estimate the cost > of each operation that the optimizer produces. > > I only found this for the sequential scan: > SEQ SCAN = ( cpu_tuple_cost * rows ) + ( number of pages * seq_page_cost ) > where : > cpu_tuple_cost=0.01 > seq_page_cost=1.0 > and "rows" and "number of pages" are given in the query plan (via > EXPLAIN ANALYZE) > > I am wondering where could I find the rest formulas for the rest operations > (e.g. HashAggregate, Nested Loop, Hash Join, Index Scan, Sort, etc) > > I also looked at costsize.c but could not find a formula like the above > or maybe I > couldn't make sense of it > > > Thank you in advance! -- Daniel Bausch Wissenschaftlicher Mitarbeiter Technische Universität Darmstadt Fachbereich Informatik Fachgebiet Datenbanken und Verteilte Systeme Hochschulstraße 10 64289 Darmstadt Germany Tel.: +49 6151 16 6706 Fax: +49 6151 16 6229
The tool to tweak the query planner parameters mentioned in the article sounds very useful. Can we download it somewhere, either as binary or source code ?
Sébastien
On Mon, Apr 8, 2013 at 2:44 AM, Daniel Bausch <bausch@dvs.tu-darmstadt.de> wrote:
Hi,
AFAIK there is no such thing in the code or documentation.
Nevertheless, for a paper I've published last year, I tried to create
mathematical transcriptions at least for the I/O costs.
http://dl.acm.org/citation.cfm?id=2236584.2236588&coll=DL&dl=ACM&CFID=309621631&CFTOKEN=77712718
Have a look at the appendix.
Regards,
Daniel Bausch
Am 06.04.2013 12:51, schrieb dafNi:--> Hello everybody!
>
> I'm trying to understand the the query planner's cost estimator.
> I was not able to find anywhere the formulas that estimate the cost
> of each operation that the optimizer produces.
>
> I only found this for the sequential scan:
> SEQ SCAN = ( cpu_tuple_cost * rows ) + ( number of pages * seq_page_cost )
> where :
> cpu_tuple_cost=0.01
> seq_page_cost=1.0
> and "rows" and "number of pages" are given in the query plan (via
> EXPLAIN ANALYZE)
>
> I am wondering where could I find the rest formulas for the rest operations
> (e.g. HashAggregate, Nested Loop, Hash Join, Index Scan, Sort, etc)
>
> I also looked at costsize.c but could not find a formula like the above
> or maybe I
> couldn't make sense of it
>
>
> Thank you in advance!
Daniel Bausch
Wissenschaftlicher Mitarbeiter
Technische Universität Darmstadt
Fachbereich Informatik
Fachgebiet Datenbanken und Verteilte Systeme
Hochschulstraße 10
64289 Darmstadt
Germany
Tel.: +49 6151 16 6706
Fax: +49 6151 16 6229
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Sebastien, > The tool to tweak the query planner parameters mentioned in the article > sounds very useful. Can we download it somewhere, either as binary or > source code ? It is currently not publicly available, because it contains some specifics (no secrets) of the experiments I did, the most prominent being that it depends on a modified version of dbt3. The changes to dbt3 in turn are even harder to share, because the master of dbt3 got heavily modified in the meantime, making a rebase hard. If you are still interested, please contact me off list. Regards, Daniel Bausch -- Daniel Bausch Wissenschaftlicher Mitarbeiter Technische Universität Darmstadt Fachbereich Informatik Fachgebiet Datenbanken und Verteilte Systeme Hochschulstraße 10 64289 Darmstadt Germany Tel.: +49 6151 16 6706 Fax: +49 6151 16 6229