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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-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

Browse pgsql-hackers by date

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

Browse pgsql-performance by date

  From Date Subject
Next Message Rigmor Ukuhe 2003-09-24 10:09:37 Index problem
Previous Message Shridhar Daithankar 2003-09-24 06:06:02 Re: LIKE query running slow