factors determining the execution plan

From: Jenny Zhang <jenny(at)osdl(dot)org>
To: Postgresql General <pgsql-general(at)postgresql(dot)org>
Cc: osdldbt-general(at)lists(dot)sourceforge(dot)net
Subject: factors determining the execution plan
Date: 2003-08-18 17:55:57
Message-ID: 1061229358.9083.246.camel@ibm-a
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

The osdl-dbt3 test starts with building and vacuuming the database.
The execution plans were taken after the vacuuming.

I did two tests with the same database parameters:
1. run two osdl-dbt3 runs on one box without rebooting the stystem.
Though the execution plans are the same, the costs are different. The
system status are different for the two runs, for example, some page
cache are not released after the first one. Does that make the cost
different?

2. run two osdl-dbt3 runs on two boxes. The hardware of the two boxes
are the same. And each run starts from scratch (building linux kernel,
pgsql ect, and reboot). To my surprise, not only the cost are different
between the two runs, the execution plan changed for Query 9.
The execution plans can be found at:
http://khack.osdl.org/stp/277780/results/plan/power_query9.txt
http://khack.osdl.org/stp/277779/results/plan/power_query9.txt

My test leads me to the following questions:
What are the factors which determine the execution plan and cost?
Does PostgreSQL check the system resource dynamically?
If Inserts and Updates happened but vacuuming is not executed, dose the
execution plan change?

Thanks,
--
Jenny Zhang
Open Source Development Lab Inc
12725 SW Millikan Way
Suite 400
Beaverton, OR 97005
(503)626-2455 ext 31

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2003-08-18 17:59:01 Re: Simulating sequences
Previous Message Tom Lane 2003-08-18 17:37:46 Re: can't find files in pg_clog and invalid page headers