Revision 8218
Added by Aaron Marcuse-Kubitza almost 12 years ago
| inputs/FIA/occurrence_all/import | ||
|---|---|---|
| 7 | 7 |
DROP VIEW IF EXISTS occurrence_all; |
| 8 | 8 |
CREATE OR REPLACE VIEW occurrence_all AS |
| 9 | 9 |
SELECT * |
| 10 |
/* directional joins needed for PostgreSQL query planner to avoid slow sorts |
|
| 11 |
(when LIMIT <= 1.3 million rows) */ |
|
| 12 |
FROM ("REF_RESEARCH_STATION"
|
|
| 13 |
NATURAL RIGHT JOIN ("REF_UNIT"
|
|
| 14 |
NATURAL RIGHT JOIN ("COUNTY"
|
|
| 15 |
NATURAL RIGHT JOIN ("PLOT"
|
|
| 16 |
NATURAL RIGHT JOIN ("COND"
|
|
| 17 |
NATURAL LEFT JOIN "REF_HABTYP_DESCRIPTION" |
|
| 18 |
NATURAL RIGHT JOIN ("SUBPLOT"
|
|
| 19 |
NATURAL RIGHT JOIN ("TREE"
|
|
| 20 |
NATURAL LEFT JOIN "REF_SPECIES" |
|
| 21 |
NATURAL LEFT JOIN "REF_PLANT_DICTIONARY" |
|
| 22 |
))))))) |
|
| 23 |
ORDER BY "individualObservationID" |
|
| 10 |
/* directional joins needed for PostgreSQL query planner to avoid slow sorts */ |
|
| 11 |
FROM "REF_RESEARCH_STATION" |
|
| 12 |
NATURAL JOIN "REF_UNIT" |
|
| 13 |
NATURAL JOIN "COUNTY" |
|
| 14 |
NATURAL JOIN "PLOT" |
|
| 15 |
NATURAL JOIN "COND" |
|
| 16 |
NATURAL LEFT JOIN "REF_HABTYP_DESCRIPTION" |
|
| 17 |
NATURAL JOIN "SUBPLOT" |
|
| 18 |
NATURAL JOIN "TREE" |
|
| 19 |
NATURAL LEFT JOIN "REF_SPECIES" |
|
| 20 |
NATURAL LEFT JOIN "REF_PLANT_DICTIONARY" |
|
Also available in: Unified diff
inputs/FIA/occurrence_all/import: Changed all RIGHT JOINs to inner joins so that tables would be joined in path order (i.e. general->specific). This optimizes the incremental joins so that the small tables are joined to each other before being joined to the large tables, rather than each row of the large tables being looked up in the small tables. This effect may not be noticeable for small LIMIT values, but would become apparent for large LIMIT values, such as the 1-million-row partitions used by db_xml.put_table() for column-based import. Note that inner joins used to cause the query planner to produce incorrect results containing slow sorts, but now this appears to no longer be an issue, perhaps because the result is not sorted by the TREE.ID index (which is not in the same order as the path indexes *.unique, *.parent).