1. Cost-based optimizer (cost-based plan);
It is the cost of reading statements, where the cost is mainly CPU and memory, the optimizer in determining whether to use this method, the main reference is the table and index statistical information;
Statistics give information about the size of the table, how many rows, the length of each row, and so on. These statistics at first in the library is not, is to do analyze after the appearance, a lot of time to expire statistics
Information will cause the optimizer to make an incorrect execution plan, which should be updated in a timely manner.
2. Rule-based Optimizer (rule-based plan):
When the optimizer analyzes SQL statements, it follows some of the rules that are predetermined within Oracle. For example, we usually go to the index when a column in a WHERE clause has an index.
Trace: http://www.poluoluo.com/jzxy/200812/54048.html
3. Explain (analyze,verbose,costs,buffers,timing) select * from XXXX;
Explain parameter explanation:
> ANALYZE: Execute command and display execution events, default false
> VERBOSE: Provide additional information to the execution plan, such as query field information, default false
> COSTS: Display execution plan, default True
> Buffers: Default False, the precondition is analyze
> Format: The default format is text
The cost constants for PostgreSQL are as follows:
> Seq_page_cost-The cost of a single block of continuous block scanning operations. such as full table scans
> Random_page_cost-The cost of a single block of random block scanning operations. such as index scans
> Cpu_tuple_cost-CPU overhead for processing each record (tuple: One row of records in a relationship)
> Cpu_index_tuple_cost--scanning CPU overhead for each index entry
> Cpu_operator_cost-The CPU overhead associated with an operator or function. (need to pay attention to function and operator of the function of the three states, the execution plan will be based on three states to do optimization, related to multiple records when the three-state corresponding call times is to be concerned about)
4. Some terms and keywords in the implementation plan.
Execution schedule Operation type operation description whether there is a start time
-------------------------------------------------------------------
Seq Scan Scan Table no boot time
Index Scan indexing scan without boot time
Bitmap index Scan indexing scan has start time
Bitmap Heap Scan Index Scan has start time
Subquery Scan subquery No boot time
Tid Scan Ctid = ... Conditional No start time
function Scan functions scan without boot time
Nested Loop loop with no start time
Merge Join merging with start time
Hash Join hashing combined with start time
Sort sorted, order by action has start time
Hash hash operation has start time
result function scan, and specific table independent no boot time
Unique distinct,union operation has start time
Limit Limit,offset operation has start time
Aggregate count, Sum,avg, StdDev aggregate function has start time
Group GROUP by group operation has start time
Append Union operation no boot time
Materialize subquery has start time
Setop Intercect,except has a boot time
Reference Address: http://my.oschina.net/congqian/blog/135872
