Revision 12621
Added by Aaron Marcuse-Kubitza over 10 years ago
| vegbien.sql | ||
|---|---|---|
| 1945 | 1945 |
|
| 1946 | 1946 |
COMMENT ON FUNCTION source_by_shortname(shortname text, schema_anchor anyelement) IS ' |
| 1947 | 1947 |
usage: |
| 1948 |
-- try each of these and use whichever creates the better query plan: |
|
| 1949 |
SELECT public.source_by_shortname(''datasource'') AS source_id;
|
|
| 1948 | 1950 |
SELECT (SELECT public.source_by_shortname(''datasource'')) AS source_id;
|
| 1949 |
/* *must* be run as a nested SELECT, because otherwise Postgres apparently will |
|
| 1950 |
not inline it. not inlining causes the query to be run for *each* row in a table |
|
| 1951 |
of potentially millions, and creates a significant slowdown (eg. >~1.5 hours |
|
| 1952 |
("5013s") for SALVIAS._plots_06_list_of_plots_with_stem_measurements) */
|
|
| 1951 |
/* *sometimes*, this must instead be run as a nested SELECT, because in some |
|
| 1952 |
cases, Postgres apparently will not inline it[1]. *however*, in other cases, you |
|
| 1953 |
must *not* use a nested SELECT, because this will prevent Postgres from using an |
|
| 1954 |
index scan (causing an equally bad slowdown). |
|
| 1955 |
|
|
| 1956 |
[1] not inlining causes the query to be run for *each* row in a table of |
|
| 1957 |
potentially millions, and creates a significant slowdown; eg. >~1.5 hours |
|
| 1958 |
("5013s") for SALVIAS._plots_06_list_of_plots_with_stem_measurements.
|
|
| 1959 |
*/ |
|
| 1953 | 1960 |
'; |
| 1954 | 1961 |
|
| 1955 | 1962 |
|
Also available in: Unified diff
bugfix: schemas/vegbien.sql: source_by_shortname(): documented that in some cases, it is actually a bad idea to use a nested SELECT, because this will prevent Postgres from using an index scan (causing an equally bad slowdown as not inlining in cases where a nested SELECT is required).