Bug 1708422 - Queries on taxable_taxonomies table running very slow
Summary: Queries on taxable_taxonomies table running very slow
Alias: None
Product: Red Hat Satellite
Classification: Red Hat
Component: Organizations and Locations
Version: 6.4.2
Hardware: Unspecified
OS: Unspecified
Target Milestone: 6.6.0
Assignee: Lukas Zapletal
QA Contact: Peter Ondrejka
Depends On:
TreeView+ depends on / blocked
Reported: 2019-05-09 20:34 UTC by sthirugn@redhat.com
Modified: 2019-10-22 19:50 UTC (History)
5 users (show)

Fixed In Version: foreman-
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
Last Closed: 2019-10-22 19:50:12 UTC
Target Upstream Version:

Attachments (Terms of Use)

System ID Private Priority Status Summary Last Updated
Foreman Issue Tracker 26777 0 Normal Closed add index on taxable_taxonomies table 2021-02-09 01:18:09 UTC

Description sthirugn@redhat.com 2019-05-09 20:34:47 UTC
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:

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);

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:

Comment 3 Lukas Zapletal 2019-05-10 08:12:01 UTC
Created redmine issue https://projects.theforeman.org/issues/26777 from this bug

Comment 5 Bryan Kearney 2019-05-10 10:04:44 UTC
Upstream bug assigned to lzap

Comment 6 Bryan Kearney 2019-05-10 10:04:45 UTC
Upstream bug assigned to lzap

Comment 7 sthirugn@redhat.com 2019-05-10 13:15:45 UTC
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.

Comment 8 sthirugn@redhat.com 2019-05-10 13:17:38 UTC
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.

Comment 9 Bryan Kearney 2019-06-22 12:05:14 UTC
Moving this bug to POST for triage into Satellite 6 since the upstream issue https://projects.theforeman.org/issues/26777 has been resolved.

Comment 11 Peter Ondrejka 2019-08-28 13:12:02 UTC
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 |
    "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;


 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)

Comment 12 Bryan Kearney 2019-10-22 19:50:12 UTC
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.


Note You need to log in before you can comment on or make changes to this bug.