472,362 Members | 2,146 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,362 software developers and data experts.

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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #1
2 2178
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_statistics_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
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....
1
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...
1
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...
3
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...
2
by: Marie | last post by:
Hi, Im 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...
2
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...
2
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...
4
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...
5
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...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
1
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...

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.