How to Guidance of Query Tuning PostgreSQL

In this article will provide How to Guidance of Query Tuning PostgreSQL. Now that you just grasp that statements square measure acting poorly and ready see their execution plans, it's time to begin tweaking the question to urge higher performance. this can be wherever you create changes to the queries and/or add indexes to undertake and find a far better execution arrange. begin with the bottlenecks and see if there square measure changes you'll create that scale back prices and/or execution times.

A note concerning knowledge cache and examination apples to apples

As you create changes and valuate Query Tuning PostgreSQL the resuling execution plans to examine if it's higher, it's vital to grasp that future executions can be relying upon knowledge caching that yield the perception of higher results. If you run a question once, create a tweak and run it a second time, it's seemingly it'll run a lot of quicker although the execution arrange isn't additional favorable. this can be as a result of PostgreSQL might need cached knowledge utilized in the primary run and is ready to use it within the second run. Therefore, you ought to run queries a minimum of three times and average the results to check apples to apples.

OK, Here are some how to Guidance of Query Tuning PostgreSQL that will facilitate recover execution plans:

1. Indexes

• Eliminate serial Scans (Seq Scan) by adding indexes (unless table size is small)
• If employing a multicolumn index, ensure you listen to order within which you outline the enclosed columns - additional data
• Try to use indexes that square measure extremely selective on commonly-used knowledge. this can create their use additional economical.

2. WHERE clause

• Avoid LIKE
• Avoid operate calls in wherever clause
• Avoid giant IN() statements

3. JOINs

• When connection tables, attempt to use an easy equality statement within the ON clause (i.e. a.id = b.person_id). Doing thus permits additional economical be part of techniques to be used (i.e. Hash be part of instead of Nested Loop Join)
• Convert subqueries to affix statements once potential as this typically permits the optimizer to know the intent and probably selected a far better arrange
• Use be part ofs properly: square measure you exploitation cluster BY or DISTINCT simply because you're obtaining duplicate results? This typically indicates improper JOIN usage and will end in a better prices
• If the execution arrange is employing a Hash be part of it is terribly slow if table size estimates square measure wrong. Therefore, ensure your table statistics square measure correct by reviewing your vacuuming strategy
• Avoid related to subqueries wherever possible; they will considerably increase question value
• Use EXISTS once checking for existence of rows supported criterion as a result of it “short-circuits” (stops process once it finds a minimum of one match)

4. General pointers

• Do additional with less; computer hardware is quicker than I/O
• Utilize Common Table Expressions and temporary tables once you have to be compelled to run bound queries
• Avoid LOOP statements and like SET operations
• Avoid COUNT(*) as PostgresSQL will table scans for this (versions <= nine.1 only)
• Avoid ORDER BY, DISTINCT, GROUP BY, UNION once potential as a result of these cause high startup prices
• Look for an outsized variance between calculable rows and actual rows within the justify statement. If the count is extremely totally different, the table statistics might be superannuated and PostgreSQL is estimating value exploitation inaccurate statistics. For example: Limit (cost=282.37..302.01 rows=93 width=22) (actual time=34.35..49.59 rows=2203 loops=1). The calculable row count was ninety three and therefore the actual was two,203. Therefore, it's seemingly creating a foul arrange call. you ought to review your vacuuming strategy and guarantee ANALYZE is being run ofttimes enough.

How to Understand SQL Index and Where should I use an index

An index is used to speed up searching in the database or An index makes the query fast” is the most basic explanation of an index I have ever seen. Although it describes the most important aspect of an index very well, it is—unfortunately—not sufficient for this book.

OK, Let's Understand the SQL Index. 

Searching in a database index is like searching in a printed telephone directory. The key concept is that all entries are arranged in a well-defined order. Finding data in an ordered data set is fast and easy because the sort order determines each entry's position.

A database index is, however, more complex than a printed directory because it undergoes constant change. Updating a printed directory for every change is impossible for the simple reason that there is no space between existing entries to add new ones. A printed directory bypasses this problem by only handling the accumulated updates with the next printing. An SQL database cannot wait that long. It must process insert, delete and update statements immediately, keeping the index order without moving large amounts of data.

• Basic Configuration Parameter for Tuning Your PostgreSQL Server
• Configuration parameter Tunables that to avoid
• Tuning Case : Tuning postgresql for large amounts of ram 

The database combines two data structures to meet the challenge: a doubly linked list and a search tree. These two structures explain most of the database's performance characteristics

And Where should I use an Index ?

An index can be used to efficiently find all row matching some column in your query and then walk through only that subset of the table to find exact matches. If you don't have indexes on any column in the WHERE clause, the SQL server have to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.


The index can also be a UNIQUE index, which means that you cannot have duplicate values in that column, or a PRIMARY KEY which in some storage engines defines where in the database file the value is stored.

In POSTGRES  you can use EXPLAIN in front of your SELECT statement to see if your query will make use of any index. This is a good start for troubleshooting performance problems.

#

Query enable parameters and Logging Parameter

It's possible to disable many of the query planner's techniques, in hopes of avoiding a known bad type of query. This is sometimes used as a work-around for the fact that PostgreSQL doesn't support direct optimizer hints for how to execute a query.

You might see the following code snippet, suggested as a way to force use of indexes instead of sequential scans for example:

enable_seqscan = off

Generally this is a bad idea, and you should improve the information the query optimizer is working with so it makes the right decisions instead.

Logging Parameter

General logging setup is important but it is somewhat outside the scope of this article. You may need to set parameters such as log_destination, log_directory, and log_filename to save your log files in a way compatible with the system administrations requirements of your environment. These will all be set to reasonable defaults to get started with on most systems.

On UNIX-like systems, it's common for some of the database logging to be set in the script that starts and stops the server, rather than directly in the postgresql.conf file. If you instead use the pg_ctlcommand to manually start the server, you may discover that logging ends up on your screen instead. You'll need to look at the script that starts the server normally (commonly /etc/init.d/postgresql) to determine what it does, if you want to duplicate that behavior. In most cases, you just need to add –l logfilename to the pg_ctl command line to redirect its output to the standard location.