Directory
- Database query pre-preparation
- VACUUM
- ANALYZE
- Explain execution plan
- Two kinds of aggregation methods
- Association
- Re-distribution
- Query optimization
- Explain parameters
- Select the appropriate distribution key
- Partition table
- Compression table
- Grouping extensions
- Window functions
- column storage and row storage
- Functions and stored procedures
- Index use
- Notin
- Too many aggregation functions
- Resource queue
- Other optimization techniques
- Reference
Database query pre-prepare vacuum
- Vacuum is simply a recycling space and allows it to be used again, without a request to lock it, and still be able to read and write to the table
- Vacuum full performs a wider range of processing, including moving rows across blocks, to compress the table to the minimum number of disk blocks stored. The relative vacuum is slow and will request an exclusive lock.
- Regular execution: In daily maintenance, the data dictionary needs to be periodically vacuum, which can be done every day when the database is idle. Then a vacuum full is performed on the system table for a longer period of time (two or three months), which requires downtime and is time consuming, and the large table can take several hours.
- Reindex: After vacuuming, it is best to rebuild the indexes on the table
ANALYZE
- Command: Analyze [talbe [(Column,..)]]
- Collects statistical information about the contents of the table to optimize the execution plan. When the index is created, the command is executed and the index is used for the query.
- Automatic statistical information collection
- In postgresql.conf, there are parameter Gp_autostats_mode settings that control automatic collection, Gp_autostats_mode three values: None, No_change, On_no_stats (default)
- None: Collect statistics is forbidden
- On change: When a DML executes after the number of rows affected exceeds the value specified by the Gp_autostats_on_change_threshold parameter, it executes the DML and then automatically performs a analyze operation to collect the statistics for the table.
- No_no_stats: When using Create Talbe as SELECT, Insert, copy, if statistics are not collected in the target table, analyze is automatically executed to gather information about this table. GP uses On_no_stats by default and consumes smaller databases, but for constantly changing tables, the database is no longer collected after the first collection of statistics. The analyze needs to be executed on an artificial schedule.
If there is a lot of SQL running time under 1 minutes, you will find a lot of time spent on collecting statistics. To reduce the consumption of this part, you can specify that statistics are not collected for some columns, as follows:
1. create table test(id int, name text,note text);
The above is known that the table column note does not need to appear on the Join column, nor does it appear under the filter condition of the WHERE statement, because this column can be set to not collect statistics:
1. alter table test alter note SET STATISTICS 0;
Explain execution plan
Shows the execution plan generated by the planner for the provided statement.
- Cost: Returns the start time before the first row is recorded, and the total time to return all records (Disk page access as
Unit metering)
- Rows: Estimate the number of rows in the SQL return result set based on statistical information
- Width: Returns the length of each row of the result set, which is based on the statistics in the Pg_statistic table
To calculate the.
Two kinds of aggregation methods
- Hashaggregate
The hash value is calculated from the value following the group by field, and the corresponding list is maintained in memory based on the aggregate function used earlier, and several of the aggregate functions have several arrays. With the same amount of data, the smaller the number of repetitions of the aggregated field, the greater the memory used.
- Groupaggregate
First, the data in the table is sorted by the Group by field, and the ordered data is fully scanned and the aggregation function is calculated. Consumption of memory is basically constant.
- Choose
There are a lot of aggregation functions in SQL, and when the group by has a smaller number of fields, it should be used Groupaggregate
Association
Divided into three categories: hash join, Nestloop join, Merge Join, in the premise of ensuring that SQL execution is correct, the planner takes precedence of hash join.
- Hash join: Computes the hash value for one of the associated tables, saves it in memory with a hash list, and then scans the other table for a full table, and then associates each row with the hash list.
- Nestedloop: The amount of data in the associated two tables is broadcast over a smaller table, such as a Cartesian product:
select * fromtest1，test2
- Merge join: The two tables are sorted by association key, and the data is associated in the same way as merge sort, which is less efficient than hash join. Full outer join can only be implemented with merge join.
- Associated broadcast and redistribution parsing P133, the General planner automatically chooses the optimal execution plan.
- Sometimes lead to redistribution and broadcast, more time-consuming operations
Re-distribution
In some SQL queries, the data needs to be re-distributed at each node, subject to network transport, disk I/O, and the redistribution rate is slow.
- Association key coercion Type conversion
In general, the table follows the assigned distribution key as a hash division. If two tables are distributed by Id:intege, ID:NUMERICR, and associated, a table ID is required for forced type conversions because different types of hash values are not the same, resulting in data redistribution.
- Association key is inconsistent with partial key
- Group BY, window function, grouping sets will cause redistribution
Query optimization
The execution plan is observed through explain to determine if SQL is optimized.
Explain parameters
Shows the execution plan generated by the planner for the provided statement.
- Cost: Returns the start time before the first row is recorded, and the total time to return all records (measured in disk page access)
- Rows: Estimate the number of rows in the SQL return result set based on statistical information
- Width: The length of each row of the returned result set, calculated based on the statistics in the Pg_statistic table.
Select the appropriate distribution key
Improper selection of distribution keys can result in redistribution, uneven distribution of data, and uneven distribution of data results in the execution of SQL in a segment node, limiting the overall speed of GP.
- So that all the node data storage is uniform, the data evenly distributed in order to make full use of multiple machine query, play a distributed advantage.
- Join, open Window function and so on as far as possible with the distribution key as the association key, partition key. In particular, it is important to note that the join, the window function will be based on the correlation key, partition key redistribution or broadcast operation, so if the distribution keys and association keys are inconsistent, no matter how to modify the distribution key, it is necessary to redistribute again.
- Try to ensure that the storage of the result set produced by the Where condition is as uniform as possible.
- To see if a table is unevenly distributed:
select gp_segment_id,count(*) from fact_tablegroup by gp_segment_id
- At the segment level, you can
select gp_segment_id,count(*) from fact_table group by gp_segment_id check whether the data in each table is evenly stored
- At the system level, the disk or directory data can be checked directly with df-h or du-h
- View a table of data skew in the database
First, the data skew rate is defined as: Maximum child node data volume/Average node data volume. To avoid the empty data volume of the whole table, and the effect on the result is very small, with a small value on the basis of the average node data, SQL is as follows:
1.SELECTTabName,2. Max(SIZE)/(avg(SIZE)+0.001) asMax_div_avg,3. sum(SIZE) total_size4. from5.(SELECTGP_SEGMENT_ID,6.Oid::regclass TabName,7.Pg_relation_size (OID)SIZE8. fromGp_dist_random (' Pg_class ')9. WHERERelkind=' R '. andRelstorageinch(' A ',' h ')) TOne by one . GROUP byTabName. ORDER by 2 DESC;
Partition table
Partitioning according to a field does not affect the distribution of data on the data nodes, but only on a single data node, the data is partitioned for storage. You can speed up the query for a partitioned field.
Compression table
Compression is used for large AO tables and partitioned tables to conserve storage space and to increase system I/O, or you can configure compression at the field level. Application Scenarios:
- Do not need to update and delete the table
- When accessing a table, it is basically a full table scan and does not need to be indexed
- You cannot frequently add fields to a table or modify field types
Grouping extensions
The group by extension of the Greenplum database can perform some common calculations and is more efficient than an application or stored procedure.
GROUPBY ROLLUP(col1, col2, col3) GROUPBY CUBE(col1, col2, col3) GROUPBY GROUPING SETS((col1, col2), (col1, col3))
ROLLUP calculates the aggregate count from the most detailed level to the topmost level on a grouping field (or expression). The rollup parameter is an ordered grouping field list that calculates aggregations from right-to-left levels. For example, ROLLUP (C1, C2, C3) will compute the aggregation for the following grouping conditions:
(c1, c2, c3) (c1, c2) (c1) ()
CUBE calculates aggregations for all combinations of grouped fields. For example, CUBE (C1, C2, C3) calculates aggregations:
(c1, c2, c3) (c1, c2) (c2, c3) (c1, c3) (c1) (c2) (c3) ()
GROUPING sets specifies that the aggregation is computed for those fields, which can control the partitioning conditions more precisely than rollup and cube.
Window functions
Window functions can implement aggregation or ranking functions on subsets of the result set, such as SUM (population) over (partition by city). Window functions are powerful and feature excellent performance. Because it computes inside the database, it avoids data transfer.
- The Window function row_number () calculates the line number of a row in a grouped sub-set, such as Row_number () over (an order by ID).
- If the query plan shows that a table has been scanned multiple times, the window function may reduce the number of scans.
- Window functions can often avoid using self-correlation.
column storage and row storage
Columnstore data in the same column is continuously stored in a physical file, with a higher compression rate, which is suitable for filtering some fields in the section table.
It is important to note that if there are more nodes in the cluster and there are more columns in the table, each column of each node will produce at least one file, then the overall result will be more files, and the DDL operation on the table will be slower. When used with partitioned tables, more files will be generated and may even exceed the file handle limit for Linux, especially if you are aware of them.
- Row storage: If the record requires Update/delete, you can only select the non-compressed row store mode. For queries, if the number of columns you select often exceeds more than 30 columns, you should also choose how you want to store the rows.
- Columnstore: If the number of columns selected is very limited and you want better IO performance for large data queries with a higher compression ratio, you should choose the Columnstore mode. Where the partition table is stored, each column of each partition will have a corresponding physical file, so be careful to avoid too many files, which can lead to the possibility of exceeding the maximum number of simultaneous open files on Linux and the poor efficiency of the DDL commands.
Functions and stored procedures
Although cursors are supported, try not to manipulate the data using cursors, but instead manipulate the data as a whole.
Index use
- If you are returning very small result sets (no more than 5%) from a very large result set, we recommend using the Btree index (atypical Data warehouse operations)
- The storage order of table records is best consistent with the index, which can further reduce IO (good index cluster)
- The columns in the Where condition join by or, consider using an index
- When key values are repeated in large numbers, it is more appropriate to use the bitmap index
For an index use test see GP Index Tuning Test – Basic and GP Index tuning test – sort article.
Not in
Too many aggregation functions
- There are too many aggregate functions in one SQL, and may be out of memory because the statistics are not detailed enough or SQL is too responsible for the wrong choice of hashaggregate.
- Workaround:
- Split into multiple SQL to execute, reducing memory used by hashaggregate
- Execute Enable_hashagg=off, turn off the hashaggregate parameter, force not to use. will be used groupaggregate, so that the sorting time, but the memory is controllable, it is recommended to use this method is relatively simple.
Resource queue
Data writes, queries use different users, and GP assigns different resource queues to different users when they create users.
Other optimization techniques
- Overwrite distinct with GROUP by, because distinct to sort operations
- Use UNION ALL Plus group by to overwrite union
- Try to use the aggregate functions and window functions provided by Greenplum itself to complete some complex analysis.
Reference
- Greenplum Enterprise Application Combat
- "Greenplum Database Best Practices", chapter seventh
Greenplum Optimization--sql Tuning Chapter
