optimizer's cost formulas

Started by dafNi zafabout 13 years ago4 messagesgeneral

dzaf88@gmail.com

about 13 years ago

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

bausch@dvs.tu-darmstadt.de

about 13 years ago

In reply to: dafNi zaf (#1)

Re: optimizer's cost formulas

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

Sébastien Lorion

sl@thestrangefactory.com

about 13 years ago

In reply to: Daniel Bausch (#2)

Re: optimizer's cost formulas

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:

Show quoted text

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

Daniel Bausch

bausch@dvs.tu-darmstadt.de

almost 13 years ago

In reply to: Sébastien Lorion (#3)

Re: optimizer's cost formulas

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general