473,587 Members | 2,487 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

factors determining the execution plan

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/res...wer_query9.txt
http://khack.osdl.org/stp/277779/res...wer_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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 11 '05 #1
2 2256
Hi,

Last week I reported getting different execution plans for the same
query against the same database. I did further investigation. After
building the database, I did:
vacuumdb -z DBT3
psql DBT3 -c "analyze supplier"
psql DBT3 -c "analyze part"
psql DBT3 -c "analyze partsupp"
psql DBT3 -c "analyze customer"
psql DBT3 -c "analyze orders"
psql DBT3 -c "analyze lineitem"
psql DBT3 -c "analyze nation"
psql DBT3 -c "analyze region"

Then I check the data in pg_class and pg_statistic and get the execution
plans.

I did this twice (say run_8 and run_9). I found that while the pg_class
are the same for run_8 and run_9, the pg_statistic output are
different. I posted the query, the execution plan, pg_class and
pg_statistic output at:
http://www.osdl.org/archive/jenny/

The difference in pg_statistic results in a different execution plan for
query 18.sql. In fact, I updated the pg_statistic table for run_9 with
the values got from run_8, then it gives me the run_8 execution plan,
and the cost is very close(within 1). This makes me to believe that the
optimizer picks the plan based on the values in pg_statistic and
pg_class.

But why the pg_statistic value changes each time I build the database?
Did I do something wrong?

Thanks for your help,
Jenny

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #2

On 22 Aug 2003, Jenny Zhang wrote:
Last week I reported getting different execution plans for the same
query against the same database. I did further investigation. After
building the database, I did:
vacuumdb -z DBT3
psql DBT3 -c "analyze supplier"
psql DBT3 -c "analyze part"
psql DBT3 -c "analyze partsupp"
psql DBT3 -c "analyze customer"
psql DBT3 -c "analyze orders"
psql DBT3 -c "analyze lineitem"
psql DBT3 -c "analyze nation"
psql DBT3 -c "analyze region"

Then I check the data in pg_class and pg_statistic and get the execution
plans.

I did this twice (say run_8 and run_9). I found that while the pg_class
are the same for run_8 and run_9, the pg_statistic output are
different. I posted the query, the execution plan, pg_class and
pg_statistic output at:
http://www.osdl.org/archive/jenny/

The difference in pg_statistic results in a different execution plan for
query 18.sql. In fact, I updated the pg_statistic table for run_9 with
the values got from run_8, then it gives me the run_8 execution plan,
and the cost is very close(within 1). This makes me to believe that the
optimizer picks the plan based on the values in pg_statistic and
pg_class.

But why the pg_statistic value changes each time I build the database?
Did I do something wrong?


Analyze only does a statistical sampling so it'd be expected that you can
get different results from analyze on the same data. There are ways to
affect the sample size and some other analyze parameters if you're
interested in look at it (default_statis tics_target and ALTER TABLE ALTER
COLUMN SET STATISTICS).


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
16500
by: (Pete Cresswell) | last post by:
I posted this in the MS Access group, but no luck. ------------------------------------------ I've got another stored procedure in the same app that returns multiple recordsets and the code works. But now I've written another SP and the code traps out with the 3251 message. The SP is writing two recordsets. When I run the SP in Query...
1
2096
by: Martin Kraus | last post by:
Hi, I have a table-valued user defined function (UDF) my_fnc. The execution of statement "select * from my_fnc" takes much longer time than runnig the code inside my_fnc (with necessary changes). What can be the reason? How can I see an execution plan used for UDF? Thanks a lot
1
2010
by: Vinny | last post by:
Can anyone help me with this strange problem please? I have a stored procedure, with a parameter defined as a uniqueidentifier. The procedure does a select with a number of joins, and filters within the Where clause using this parameter. (@orderHeader_id uniqueidentifier) SELECT *
3
5361
by: Will Atkinson | last post by:
Hi All, I'm a relative newbie to SQL Server, so please forgive me if this is a daft question... When I set "Show Execution Plan" on in Query Analyzer, and execute a (fairly complex) sproc, I note that a particular query is reported as having a query cost of "71% relative to the batch" - however, this is nowhere near the slowest executing...
2
3537
by: Marie | last post by:
Hi, I´m trying to perform Fourier Transforms on contiuously incoming images from a camera. I have sofar been using the Basic Interface for planning and execution for every image. Later I have tried to only perform the basic planning once and then using the Guru Interface for execution continuously for each incoming image.
2
2044
by: Jenny Zhang | last post by:
Hi, I am running OSDL-DBT3 test against PostgreSQL. I found performance difference between the runs even though the data and queries are the same. I tried to study this problem by getting execution plans at the beginning of each test. The following script gets execution plan for 22 queries, except query 15. i=1 while
2
3723
by: Ina Schmitz | last post by:
Hi NG, does IBM Universal Database 8.2 make any difference between actual and estimated execution plans like in SQL Server ("set showplan_all on" for estimated execution plan and "set statistics profile on" for actual execution plan)? Does "explain plan selection for" generate the *estimated* execution plan? Didn't find any distinction of...
4
6954
by: TheRealPawn | last post by:
I'm trying to get the execution plan for a single stored procedure from Profiler. Now, I've isolated the procedure but I get all execution plans. Any ideas on how to connect the SPIDs so that I only get the execution plan for the procedure I'm watching and not the whole of the server?
5
10737
by: sqlgirl | last post by:
Hi, We are trying to solve a real puzzle. We have a stored procedure that exhibits *drastically* different execution times depending on how its executed. When run from QA, it can take as little as 3 seconds. When it is called from an Excel vba application, it can take up to 180 seconds. Although, at other times, it can take as little as...
0
7920
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7849
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8215
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7973
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6626
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5718
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3879
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2358
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1454
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.