Jira (PDB-1813) Run the state-overview queries as a single query.

[Andrew Roetker (JIRA)]

Title: Message Title

Andrew Roetker created an issue   PuppetDB / PDB-1813 Run the state-overview queries as a single query. Issue Type: Improvement Affects Versions: PDB 3.0.0 Assignee: Unassigned Created: 2015/07/17 2:30 PM Fix Versions: PDB 3.1.0 Priority: Normal Reporter: Andrew Roetker Scott Walker may be able to provide more context here, but it has been brought up that having the state-overview endpoint use three queries to provide its view is not fast enough for the number of nodes state-overview has to support and will only get worse in Ankeny. Unchanged resources work will require us to have PE only query-recs, as there will be PE only tables. That means we will need the ability to pass query-recs to the PuppetDB in-process `query-fn`, in addition to the `:keyword`s we pass today, e.g. we would be able to pass `reports/query->sql` or `:reports` to query the reports endpoint. We could use the same idea here as well and have a state-overview query-rec that looks like (defn state-overview-query "TODO pass the unresponsive-threshold to the query" [unresponse-threshold] (fn [] (map->Query {:projections {"last_report_status" {:type :string :queryable? true :field (hcore/raw (str "CASE WHEN certnames.latest_report_id IS NULL THEN 'unreported' " "WHEN reports.end_time < NOW() - interval '1 hour' THEN 'unresponsive' " "WHEN reports.noop = TRUE THEN 'noop' " "ELSE resport_statuses.status END"))} "certname" {:type :string :queryable? true :field :certname.certname}} :selection {:from [:certnames] :left-join [:reports [:= :certnames.latest_report_id :reports.id] :report_statuses [:= :reports.status_id :report_statuses.id]]}   :source-table "certnames" :alias "state_overview" :subquery? false}))) (here's a gist of the raw sql https://gist.github.com/ajroetker/5dff6360d92f207193eb). Using this query-rec we would pass an aggregate-query when we compile the query-rec to end up with a single query to run for the state-overview endpoint. Add Comment   This message was sent by Atlassian JIRA (v6.4.5#64020-sha1:78acd6c) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.