Project

General

Profile

Statistics
| Revision:
Name Size Revision Age Author Comment
  branches 11969 almost 11 years Aaron Marcuse-Kubitza added standard svn dirs branches, tags, trunk
  tags 11969 almost 11 years Aaron Marcuse-Kubitza added standard svn dirs branches, tags, trunk
  trunk 12521 almost 11 years Aaron Marcuse-Kubitza schemas/vegbien.sql: location.top_plot: renamed...
  • svn:ignore: extern

Latest revisions

# Date Author Comment
12521 02/27/2014 03:51 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: location.top_plot: renamed to plot_location_id, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)

12520 02/27/2014 02:20 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: source_by_shortname(): documented exact time that the slow query ran for (5013s = ~1.5 hours)

12519 02/27/2014 02:07 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations: schema comment: documented how to sync the queries with schemas/vegbien.sql

12518 02/27/2014 02:05 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: public_validations.rm_all_queries(): when called with no args, operate on public_validations instead of all schemas, since this is the more common use case (replacing just the output queries, not both input and output)

12517 02/27/2014 01:49 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: rm_query_group(): documented that this only removes validations output queries (in public_validations), as it is designed for use in updating all output queries at once from the testing DB

12516 02/27/2014 01:27 PM Aaron Marcuse-Kubitza

bugfix: *.sql: public.source_by_shortname(): need to wrap it in a nested SELECT because Postgres incorrectly does not constant-fold (inline) it, leading to a slowdown when it is therefore run many times. this is done using the steps at wiki.vegpath.org/Postgres_queries#wrap-function-call-in-nested-SELECT .

12515 02/27/2014 12:43 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: source_by_shortname(): documented that it must be run as a nested SELECT, because otherwise Postgres will not inline it. not inlining causes the query to be run for each row in a table of potentially millions, and creates a significant slowdown (eg. >1.5 hours for SALVIAS._plots_06_list_of_plots_with_stem_measurements)

12514 02/27/2014 07:59 AM Aaron Marcuse-Kubitza

schemas/util.sql: eval2val(): made it STABLE so that functions that use it can themselves be inlined. (Postgres apparently ignores the STABLE qualifier, without warning, if the function contains any VOLATILE statements.)

12513 02/27/2014 06:51 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: source_by_shortname(): needs to be declared STABLE so it can be constant-folded and only run once per query. (it had previously been VOLATILE because of constraints imposed on functions that run `SET LOCAL search_path`.)

12512 02/27/2014 01:11 AM Aaron Marcuse-Kubitza

fix: schemas/vegbien.sql: location: added index on top_plot

View all revisions | View revisions

Also available in: Atom