Project

General

Profile

« Previous | Next » 

Revision 12637

Added by Aaron Marcuse-Kubitza almost 11 years ago

web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: query planner: documented how to prevent incorrect query plans (`SET enable_seqscan = off;`, etc.)

View differences:

index.htm
748 748 
* performs a sequential scan when an index is available (because it incorrectly thinks there are too many dead rows in the index)
749 749 
* uses an in-memory hash join when an index is available (because it incorrectly thinks that accessing an index on disk will be too slow)
750 750 
* sorts *both* input tables before performing a merge join (if really no index is available, a hash join is much faster than two sorts and a merge join)
751 
* does joins in the opposite order that they should be done (because it incorrectly thinks that the last table in the join will be filtered to fewer rows than the first table, and ignores the programmer's join order)
751 
* does the entire join sequence in the opposite order (because it incorrectly thinks that the last table in the join will be filtered to fewer rows than the first table, and ignores the programmer's join order)
752 
* joins a pair of tables in the wrong order (the smaller table should be iterated over, with an index or hash lookup being used for the larger table, not vice versa)
753 

  		




  
  754
  
    
for these reasons, the following should always be off:


  		




  
  755
  
    
SET enable_seqscan = off;


  		




  
  756
  
    
also, if you turn off join_collapse_limit to force using your join order, you may need to turn off other options as well:


  		




  
  757
  
    
SET join_collapse_limit = 1; -- turn it off


  		




  
  758
  
    
SET enable_sort = off;


  		




  
  759
  
    
SET enable_hashjoin = off; -- better to keep this on if possible


  		




  
  760
  
    
SET enable_mergejoin = off;


  		




  
  761
  
    
fortunately, the combination of all of these is usually sufficient to get Postgres to use the right query plan.


  		




  752
  762
  
    
                                <DT><A HREF="http://www.postgresql.org/docs/8.3/static/functions-array.html" name="http://www.postgresql.org/docs/8.3/static/functions-array.html" ADD_DATE="1345012706" ICON_URI="http://www.postgresql.org/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://www.postgresql.org/favicon.ico" style="margin-right: 0.5em;"/>PostgreSQL: Documentation: 8.3: Array Functions and Operators</A><a name="PostgreSQL: Documentation: 8.3: Array Functions and Operators" href="#PostgreSQL: Documentation: 8.3: Array Functions and Operators" style="margin-left: 0.5em;">&para;</a>


  		




  753
  763
  
    
                                <DT><A HREF="http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html" name="http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html" ADD_DATE="1342742575" ICON_URI="http://www.postgresql.org/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://www.postgresql.org/favicon.ico" style="margin-right: 0.5em;"/>PostgreSQL: Documentation: 8.3: Control Structures</A><a name="PostgreSQL: Documentation: 8.3: Control Structures" href="#PostgreSQL: Documentation: 8.3: Control Structures" style="margin-left: 0.5em;">&para;</a>


  		




  754
  764
  
    
                                <DT><A HREF="http://www.postgresql.org/docs/8.3/static/catalog-pg-enum.html" name="http://www.postgresql.org/docs/8.3/static/catalog-pg-enum.html" ADD_DATE="1349409516" ICON_URI="http://www.postgresql.org/favicon.ico" LAST_CHARSET="UTF-8"><img width="16" height="16" src="http://www.postgresql.org/favicon.ico" style="margin-right: 0.5em;"/>PostgreSQL: Documentation: 8.3: pg_enum</A><a name="PostgreSQL: Documentation: 8.3: pg_enum" href="#PostgreSQL: Documentation: 8.3: pg_enum" style="margin-left: 0.5em;">&para;</a>


  		












Also available in:  Unified diff