Revision 12637
Added by Aaron Marcuse-Kubitza almost 11 years ago
| trunk/web/links/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;">¶</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;">¶</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;">¶</a> |
Also available in: Unified diff
web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: query planner: documented how to prevent incorrect query plans (`SET enable_seqscan = off;`, etc.)