PostgreSQL Explain

Some info about PostgreSQL Explain - mainly for my own reference:

1) Include information on buffer usage:

 explain ( ANALYZE, BUFFERS ) select ...  

Tells us how many blocks are read from disc / from the postgres cache.

2) Display shared_buffers size:

 SELECT current_setting('shared_buffers') AS shared_buffers  

Also see: Memory - shared_buffers


3)  Influence the query plans chosen by the query optimizer:

 SET enable_seqscan TO off;  
 EXPLAIN ANALYZE SELECT ...  
 SET enable_seqscan TO on;  

For more options see: Planner Method Configuration

4) Drop Linux page cache and PostgreSQL cache (by restarting PostgreSQL) 

 $ /etc/init.d/postgresql stop  
 $ sync  
 $ echo 3 > /proc/sys/vm/drop_caches  
 $ /etc/init.d/postgresql start  

 5) Update the table's statistics:

 ANALYZE [tablename]  

Stores information in "pg_statistic". Use view "pg_stats" to look at the data

6) Specify an operator class to support like queries on data stored in UTF-8:

 CREATE INDEX ON foo(myColumn text_pattern_ops);

 EXPLAIN SELECT * FROM foo WHERE myColumn LIKE 'abcd%'; 

When you use a encoding other than C, you need to specifiy the operator class (varchar_pattern_ops, text_pattern_ops, etc.) while creating the index.

7) http://explain.depesz.com/


For more information see: Understanding EXPLAIN