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

Re: osdl-dbt3 run results - puzzled by the execution

From: Manfred Koizar <mkoi-pg(at)aon(dot)at>
To: Jenny Zhang <jenny(at)osdl(dot)org>
Cc: perf-pgsql <pgsql-performance(at)postgresql(dot)org>,Matt Clark <matt(at)ymogen(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: osdl-dbt3 run results - puzzled by the execution
Date: 2003-09-24 09:14:19
Message-ID: 4sj2nvcla6v5v9e3cr96kopjtda7k6mq8v@email.aon.at (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Fri, 19 Sep 2003 11:35:35 -0700, Jenny Zhang <jenny(at)osdl(dot)org>
wrote:
>I posted more results as you requested:

Unfortunately they only confirm what I suspected earlier:

>> 2)     ->  Index Scan using i_ps_suppkey on partsupp
>>                  (cost=0.00..323.16 rows=80 width=34)
>>                  (actual time=0.16..2.98 rows=80 loops=380)
>>                  ctr=108.44

>> the planner does not
>> account for additional index scans hitting pages in the cache that
>> have been brought in by preceding scans.  This is a known problem

PF1 = estimated number of page fetches for one loop ~ 320
L   = estimated number of loops ~ 400
P   = number of pages in relation ~ 21000

Cutting down the number of heap page fetches if PF1 * L > P and P <
effective_cache_size seems like an obvious improvement, but I was not
able to figure out where to make this change.  Maybe it belongs into
costsize.c near

	run_cost += outer_path_rows *
		(inner_path->total_cost - inner_path->startup_cost) *
		joininfactor;

in cost_nestloop() or it should be pushed into the index cost
estimation functions.  Hackers?

For now you have to keep lying about effective_cache_size to make the
planner overestimate merge joins to compensate for the planner's
overestimation of nested loops.  Sorry for having no better answer.

Servus
 Manfred

In response to

Responses

pgsql-performance by date

Next:From: Rigmor UkuheDate: 2003-09-24 10:09:37
Subject: Index problem
Previous:From: Shridhar DaithankarDate: 2003-09-24 06:06:02
Subject: Re: LIKE query running slow

pgsql-hackers by date

Next:From: Hans-Jürgen SchönigDate: 2003-09-24 11:18:32
Subject: Re: ecpg build on AIX 4.2.1
Previous:From: Hornyak LaszloDate: 2003-09-24 08:00:12
Subject: Re: postgres 6.2 vacuum

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