Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Please Help: PostgreSQL Query Optimizer

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: "Anjan Kumar(dot) A(dot)" <anjankumar(at)cse(dot)iitb(dot)ac(dot)in>, pgsql-chat(at)postgresql(dot)org, pgsql-benchmarks(at)postgresql(dot)org, pgsql-docs(at)postgresql(dot)org
Subject: Re: [HACKERS] Please Help: PostgreSQL Query Optimizer
Date: 2005-12-13 06:47:21
Message-ID: 200512122247.21560.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-benchmarkspgsql-chatpgsql-docspgsql-hackers
Anjan,

> But, in PostgreSQL  all costs are  scaled relative to a page fetch. If we
> make both sequential_page_fetch_cost and random_page_cost to "1", then  we
> need to increase the various cpu_* paramters by multiplying the default
> values with appropriate  Scaling Factor.  Now, we need to determine this
> Scaling Factor.

I see, so you're saying that because the real cost of a page fetch has 
decreased, the CPU_* costs should increase proportionally because relative to 
the real costs of a page fetch they should be higher?  That makes a sort of 
sense.

The problem that you're going to run into is that currently we have no 
particularly reason to believe that the various cpu_* costs are more than 
very approximately correct as rules of thumb.  So I think you'd be a lot 
better off trying to come up with some means of computing the real cpu costs 
of each operation, rather than trying to calculate a multiple of numbers 
which may be wrong in the first place.

I know that someone on this list was working on a tool to digest EXPLAIN 
ANALYZE results and run statistics on them.   Can't remember who, though.

Also, I'm still curious on how you're handling shared_mem, work_mem and 
maintenance_mem.  You didn't answer last time.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

pgsql-docs by date

Next:From: Luiz K. MatsumuraDate: 2005-12-15 20:08:45
Subject: Pl/pgSQL documentation
Previous:From: Marc G. FournierDate: 2005-12-13 02:06:52
Subject: Re: Moving FAQs to PgFoundry

pgsql-hackers by date

Next:From: Josh BerkusDate: 2005-12-13 06:50:56
Subject: Re: Which qsort is used
Previous:From: Luke LonerganDate: 2005-12-13 05:31:20
Subject: Re: Cost-based optimizers

pgsql-chat by date

Next:From: Anjan Kumar. A.Date: 2006-01-12 23:05:45
Subject: Re: Please Help: PostgreSQL Query Optimizer
Previous:From: Jim C. NasbyDate: 2005-12-12 20:40:03
Subject: Re: Please Help: PostgreSQL Query Optimizer

pgsql-benchmarks by date

Next:From: Anjan Kumar. A.Date: 2006-01-12 23:05:45
Subject: Re: Please Help: PostgreSQL Query Optimizer
Previous:From: Jim C. NasbyDate: 2005-12-12 20:40:03
Subject: Re: Please Help: PostgreSQL Query Optimizer

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group