postgresql query optimization tips

Label: MySQL is a powerful open-source database. With more and more database-driven applications, people have been pushing MySQL to its limits. Here are 101 tips for adjusting and optimizing MySQL installation. Some of the techniques are specific to the installation environment, but these ideas are generic. I've divided them into several categories to help you master more of the tuning and optimization tech

the following test: the where part of the col_name is NULL, where the col_name is a column declared not NULL, MySQL finds a match Stop after one line of the join condition (for a specific keyword combination) to search for additional rows. The execution of the right join is similar to a left join, but the role of the table is reversed. Joins the optimizer in the order in which the tables should be joined. Left JOIN and Straight_join forced table read Order can help the Join optimizer work fas

;/*+leading (TABLE) */Takes the specified table as the first table in the join order./*+cache (TABLE) */When a full table scan is performed, the cache hint is able to place the retrieved block of the table in the buffer cache most recently used in the least recent list of LRUFor example:SELECT/*+full (BSEMPMS) Cahe (BSEMPMS) */Emp_nam from Bsempms;/*+nocache (TABLE) */When a full table scan is performed, the cache hint is able to place the retrieved block of the table in the buffer cache most re

-point numbers are converted to integers, which is more prone to error, and many people prefer to use parseint (), in fact parseint () is used to convert strings to numbers rather than floating-point and integer conversions, and we should use Math.floor () or Math.Round (). The problems in object lookup are different, and math is an internal object, so Math.floor () actually does not have much of a query method and time to call, and the speed is the f

querying the database and other operations can be changed to asynchronous operations, reduce thread congestion, and further improve CPU utilization.Scheduling multiple tasks, you can start tasks without having to worry about a task schedule, and you don't have to wait.Data structure optimizationModelConsider Model whether there are more useless fields in the use of more places, and if any, consider using a new lightweight Model , optimized Model data structure to optimize memory storage.CacheTh

, because no matter how many records are created, the index of the gender is set up so that your statements are retrieved half as little. But with the index to create the loss ratio, picking sesame lost watermelon. (may not be accurate, but to the effect).For example, the database is like a Xinhua dictionary, we check the data, can be based on pinyin to check, the word in the dictionary sort is based on pinyin to sort, we want to check a word, you can quickly find out according to pinyin we want

Homepage as the weight of the entire site page, generally the entire Web site traffic is the largest page. Seoer in the site optimization, the home page is also its most important, we understand that the degree of home optimization directly affect the site's ranking and conversion rate. But, as seoer do you know how to do home optimization? Whether it is the dai

order WHERE orderdateThe same situation can occur when a numeric field is calculated:SELECT * from inventory WHERE amount/7SELECT * from inventory WHERE amountthe two queries above also return the same result, but the subsequent query will be much faster than the previous one. Third, when searching for character fields, we sometimes use the LIKE keyword and wildcard characters, which is simple, but at the expense of system performance. For example, t

write [Session|global] The default is session sessions, referring to the execution of the current window, if you want to see all (from MySQL boot to present, then should global) Show status like ' connections '; Show Slow query times Show status like ' Slow_queries '; 2, modify the slow query MySQL. Show variables like ' long_query_time '; Can display the current slow