Bug 1708422
| Summary: | Queries on taxable_taxonomies table running very slow | ||
|---|---|---|---|
| Product: | Red Hat Satellite | Reporter: | sthirugn <sthirugn> |
| Component: | Organizations and Locations | Assignee: | Lukas Zapletal <lzap> |
| Status: | CLOSED ERRATA | QA Contact: | Peter Ondrejka <pondrejk> |
| Severity: | medium | Docs Contact: | |
| Priority: | unspecified | ||
| Version: | 6.4.2 | CC: | andrew.schofield, aruzicka, chrobert, inecas, zhunting |
| Target Milestone: | 6.6.0 | Keywords: | Triaged |
| Target Release: | Unused | ||
| Hardware: | Unspecified | ||
| OS: | Unspecified | ||
| Whiteboard: | |||
| Fixed In Version: | foreman-1.22.0.2-1 | Doc Type: | If docs needed, set a value |
| Doc Text: | Story Points: | --- | |
| Clone Of: | Environment: | ||
| Last Closed: | 2019-10-22 19:50:12 UTC | Type: | Bug |
| Regression: | --- | Mount Type: | --- |
| Documentation: | --- | CRM: | |
| Verified Versions: | Category: | --- | |
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |
| Cloudforms Team: | --- | Target Upstream Version: | |
| Embargoed: | |||
Created redmine issue https://projects.theforeman.org/issues/26777 from this bug Upstream bug assigned to lzap Upstream bug assigned to lzap Thank you Lukas for immediate action on this. Since this is an easy fix but provides excellent performance benefits to all customers, I am going to ask for 6.4.z and 6.5.z. Note to bug triage team and QE - This is a super easy bug to verify (see the description for the sql queries to test) and provides an excellent performance boost. Moving this bug to POST for triage into Satellite 6 since the upstream issue https://projects.theforeman.org/issues/26777 has been resolved. Checked on Satellite 6.6 snap 17, while not having a large env, confirming that the migration landed in downstream and the "taxonomy_id" and "taxable_type" indices are now used.
```
foreman=# \d taxable_taxonomies;
Table "public.taxable_taxonomies"
Column | Type | Modifiers
--------------+-----------------------------+-----------------------------------------------------------------
id | integer | not null default nextval('taxable_taxonomies_id_seq'::regclass)
taxonomy_id | integer |
taxable_id | integer |
taxable_type | character varying(255) |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
Indexes:
"taxable_taxonomies_pkey" PRIMARY KEY, btree (id)
"taxable_index" UNIQUE, btree (taxable_type, taxable_id, taxonomy_id)
"index_taxable_taxonomies_on_taxonomy_id_and_taxable_type" btree (taxonomy_id, taxable_type)
Foreign-key constraints:
"taxable_taxonomies_taxonomy_id_fk" FOREIGN KEY (taxonomy_id) REFERENCES taxonomies(id)
```
Rerunning the explain from the problem description:
```
foreman=# explain analyze SELECT DISTINCT "taxable_taxonomies"."taxable_id" FROM "taxable_taxonomies" WHERE
"taxable_taxonomies"."taxable_type" = 'SmartProxy' AND "taxable_taxonomies"."taxonomy_id" = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------
HashAggregate (cost=8.28..8.29 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1)
-> Index Scan using index_taxable_taxonomies_on_taxonomy_id_and_taxable_type on taxable_taxonomies (cost
=0.00..8.27 rows=1 width=4) (actual time=0.019..0.021 rows=1 loops=1)
Index Cond: ((taxonomy_id = 1) AND ((taxable_type)::text = 'SmartProxy'::text))
Total runtime: 0.051 ms
(4 rows)
```
Since the problem described in this bug report should be resolved in a recent advisory, it has been closed with a resolution of ERRATA. For information on the advisory, and where to find the updated files, follow the link below. If the solution does not work for you, open a new bug report. https://access.redhat.com/errata/RHSA-2019:3172 |
Description of problem: Queries on the taxable_taxonomies table running very slow Version-Release number of selected component (if applicable): Satellite 6.4.2 How reproducible: Always Steps to Reproduce: The following was experienced in a large customer environment: I am not yet quite sure of when this specific query will be invoked. This problem was identified by reviewing the PostgreSQL logs. foreman=# explain analyze SELECT DISTINCT "taxable_taxonomies"."taxable_id" FROM "taxable_taxonomies" WHERE "taxable_taxonomies"."taxable_type" = 'SmartProxy' AND "taxable_taxonomies"."taxonomy_id" = 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=49714.35..49715.04 rows=69 width=4) (actual time=594.969..594.975 rows=35 loops=1) -> Seq Scan on taxable_taxonomies (cost=0.00..49713.90 rows=180 width=4) (actual time=0.103..594.947 rows=35 loops=1) Filter: (((taxable_type)::text = 'SmartProxy'::text) AND (taxonomy_id = 1)) Rows Removed by Filter: 2073860 Total runtime: 595.048 ms (5 rows) Create index: foreman=# create index index_taxable_taxonomies_on_taxonomy_id_taxable_type on taxable_taxonomies (taxonomy_id,taxable_type); CREATE INDEX foreman=# explain analyze SELECT DISTINCT "taxable_taxonomies"."taxable_id" FROM "taxable_taxonomies" WHERE "taxable_taxonomies"."taxable_type" = 'SmartProxy' AND "taxable_taxonomies"."taxonomy_id" = 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------- HashAggregate (cost=695.69..696.40 rows=71 width=4) (actual time=0.097..0.099 rows=35 loops=1) -> Bitmap Heap Scan on taxable_taxonomies (cost=6.56..695.23 rows=185 width=4) (actual time=0.054..0.091 rows=35 loops=1) Recheck Cond: ((taxonomy_id = 1) AND ((taxable_type)::text = 'SmartProxy'::text)) -> Bitmap Index Scan on index_taxable_taxonomies_on_taxonomy_id_taxable_type (cost=0.00..6.51 rows=185 width=0) (actual t ime=0.047..0.047 rows=35 loops=1) Index Cond: ((taxonomy_id = 1) AND ((taxable_type)::text = 'SmartProxy'::text)) Total runtime: 0.133 ms (6 rows) Actual results: See above: Total runtime: 595.048 ms Expected results: (after table index) See above: Total runtime: 0.133 ms Additional info: