Potential regression ...
User with the following amount of data:
{code}
public | reports | table | pe-puppetdb | 42 GB |
public | reports_id_seq | sequence | pe-puppetdb | 8192 bytes |
public | resource_events | table | pe-puppetdb | 3736 MB |
public | resource_params | table | pe-puppetdb | 31 MB |
{code}
{code}
pe-puppetdb=# select count(*) from resource_events ;
count
----------
10039459
(1 row)
{code}
Adding the index from PDB-3053:
{code}CREATE UNIQUE INDEX CONCURRENTLY idx_certnames_latest_report_id ON certnames(latest_report_id){code}
... resolved slow/ Resolved timeout issues in the Console Overview, but created slow/ timeout issues when using the "Unresponsive for N+ hours" Filter in the Console Overview.
I will post queries {code}
2017-05-15 10:11:03.131 EDT [db:pe-puppetdb , and ask user for EXPLAIN ANALYZE output sess : with and without the index 5919ab15 . 2fd7,pid:12247,vtid:59/1655914,tid:0] LOG: duration: 129949.316 ms execute S_1012/C_1013:
{code}
{code}
SELECT certnames.certname AS certname, reports_environment.environment AS report_environment, reports.end_time AS report_timestamp, encode(reports.hash::bytea, 'hex') AS latest_report_hash, report_statuses.status AS latest_report_status, reports.noop AS latest_report_noop, reports.noop_pending AS latest_report_noop_pending, reports.cached_catalog_status AS cached_catalog_status, reports.corrective_change AS latest_report_corrective_change FROM certnames LEFT JOIN catalogs ON catalogs.certname = certnames.certname LEFT JOIN factsets fs ON certnames.certname = fs.certname LEFT JOIN reports ON certnames.latest_report_id = reports.id LEFT JOIN environments catalog_environment ON catalog_environment.id = catalogs.environment_id LEFT JOIN report_statuses ON reports.status_id = report_statuses.id LEFT JOIN environments facts_environment ON facts_environment.id = fs.environment_id LEFT JOIN environments reports_environment ON reports_environment.id = reports.environment_id WHERE ((reports.end_time < $1) AND ((certnames.certname) in ( (SELECT certnames.certname AS certname FROM certnames LEFT JOIN catalogs ON catalogs.certname = certnames.certname LEFT JOIN factsets fs ON certnames.certname = fs.certname LEFT JOIN reports ON certnames.latest_report_id = reports.id LEFT JOIN environments catalog_environment ON catalog_environment.id = catalogs.environment_id LEFT JOIN report_statuses ON reports.status_id = report_statuses.id LEFT JOIN environments facts_environment ON facts_environment.id = fs.environment_id LEFT JOIN environments reports_environment ON reports_environment.id = reports.environment_id WHERE (certnames.deactivated IS NULL AND certnames.expired IS NULL)) ) )) ORDER BY reports.end_time DESC, reports.end_time DESC LIMIT 50 OFFSET 0;
{code}
|
|
|
