PostgreSQL execution plan explained

Recently I ran into slow query problem in our PostgreSQL database, after doing some research, I found those tips are pretty good. Just shared here:

1. Run “Explain [analyze] your SQL statement”

  EXPLAIN SELECT * FROM my_table;  
 EXPLAIN ANALYZE SELECT * FROM my_table;  

The ANALYZE option causes the statement to be actually executed, not only planned.

For more details, check PostgreSQL official ‘explain’ document: http://www.postgresql.org/docs/current/static/sql-explain.html

2. Look for “seq scan” in execution plan result

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..483.00 rows=7033 width=244)
   Filter: (unique1 < 7000)

3. PgAdmin has the graphical explain plan feature

While a graphical explain plan is not a complete substitute for EXPLAIN or EXPLAIN ANALYZE text plans, it does provide a quick and easy to read view that can be used for further analysis.

analyze

 

Bad result (full table scan)

query-full-table-scan

 

After adding index

query-uses-index

(Visited 42 times, 1 visits today)

Leave a Reply