Bug 1486411 - Add db index on "katello_content_facet_errata" "content_facet_id"
Summary: Add db index on "katello_content_facet_errata" "content_facet_id"
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Red Hat Satellite
Classification: Red Hat
Component: Hosts - Content
Version: 6.2.12
Hardware: Unspecified
OS: Unspecified
high
medium
Target Milestone: Unspecified
Assignee: Andrew Kofink
QA Contact: Peter Ondrejka
URL:
Whiteboard:
: 1518712 (view as bug list)
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2017-08-29 16:57 UTC by Justin Sherrill
Modified: 2019-04-01 20:27 UTC (History)
4 users (show)

Fixed In Version:
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
Environment:
Last Closed: 2018-02-21 16:54:37 UTC
Target Upstream Version:


Attachments (Terms of Use)


Links
System ID Private Priority Status Summary Last Updated
Foreman Issue Tracker 21282 0 None None None 2017-10-10 16:59:43 UTC

Description Justin Sherrill 2017-08-29 16:57:18 UTC
2017-08-21 14:47:18 CEST LOG:  duration: 1001.321 ms  execute <unnamed>: SELECT "katello_content_facet_errata".* FROM "katello_content_facet_errata"  WHERE "katello_content_facet_errata"."content_facet_id" IN (8984, 9436, 9384, 9215, 9214, 9213, 9212, 9386, 9383, 8610, 9104, 8607, 8606, 8604, 13994, 9179, 8806, 16912, 9919, 8694, 8695, 8696, 8697, 8698, 9190, 8803, 8804, 8617, 9180, 8917, 9025, 8577, 9388, 9586, 13983, 10166, 14183, 11828, 14166, 14170, 14175, 13938, 13984, 14008, 13982, 16486, 13899, 10160, 10169, 10159)

Query plan shows no index:

foreman=# explain analyze SELECT "katello_content_facet_errata".* FROM "katello_content_facet_errata"  WHERE "katello_content_facet_errata"."content_facet_id" IN (8984, 9436, 9384, 9215, 9214, 9213, 9212, 9386, 9383, 8610, 9104, 8607, 8606, 8604, 13994, 9179, 8806, 16912, 9919, 8694, 8695, 8696, 8697, 8698, 9190, 8803, 8804, 8617, 9180, 8917, 9025, 8577, 9388, 9586, 13983, 10166, 14183, 11828, 14166, 14170, 14175, 13938, 13984, 14008, 13982, 16486, 13899, 10160, 10169, 10159);

                                QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------
 Seq Scan on katello_content_facet_errata  (cost=0.00..112123.95 rows=15877 width=12) (actual time=3.853..595.460 rows=3310
 loops=1)
   Filter: (content_facet_id = ANY ('{8984,9436,9384,9215,9214,9213,9212,9386,9383,8610,9104,8607,8606,8604,13994,9179,8806
,16912,9919,8694,8695,8696,8697,8698,9190,8803,8804,8617,9180,8917,9025,8577,9388,9586,13983,10166,14183,11828,14166,14170,
14175,13938,13984,14008,13982,16486,13899,10160,10169,10159}'::integer[]))
   Rows Removed by Filter: 1441228
 Total runtime: 595.654 ms
(4 rows)

foreman=# \d "katello_content_facet_errata"
                              Table "public.katello_content_facet_errata"
      Column      |  Type   |                                 Modifiers
------------------+---------+---------------------------------------------------------------------------
 id               | integer | not null default nextval('katello_content_facet_errata_id_seq'::regclass)
 content_facet_id | integer | not null
 erratum_id       | integer | not null
Indexes:
    "katello_content_facet_errata_pkey" PRIMARY KEY, btree (id)
    "katello_content_facet_errata_eid_caid" UNIQUE, btree (erratum_id, content_facet_id)
Foreign-key constraints:
    "katello_content_facet_errata_ca_id" FOREIGN KEY (content_facet_id) REFERENCES katello_content_facets(id)
    "katello_content_facet_errata_errata_id" FOREIGN KEY (erratum_id) REFERENCES katello_errata(id)

So, I created an index:

foreman=# create index "katello_content_facet_errata_caid" ON "katello_content_facet_errata" using btree(content_facet_id);
CREATE INDEX
foreman=# \d "katello_content_facet_errata"
                              Table "public.katello_content_facet_errata"
      Column      |  Type   |                                 Modifiers
------------------+---------+---------------------------------------------------------------------------
 id               | integer | not null default nextval('katello_content_facet_errata_id_seq'::regclass)
 content_facet_id | integer | not null
 erratum_id       | integer | not null
Indexes:
    "katello_content_facet_errata_pkey" PRIMARY KEY, btree (id)
    "katello_content_facet_errata_eid_caid" UNIQUE, btree (erratum_id, content_facet_id)
    "katello_content_facet_errata_caid" btree (content_facet_id)
Foreign-key constraints:
    "katello_content_facet_errata_ca_id" FOREIGN KEY (content_facet_id) REFERENCES katello_content_facets(id)
    "katello_content_facet_errata_errata_id" FOREIGN KEY (erratum_id) REFERENCES katello_errata(id)

And now:

foreman=# explain analyze SELECT "katello_content_facet_errata".* FROM "katello_content_facet_errata"  WHERE "katello_content_facet_errata"."content_facet_id" IN (8984, 9436, 9384, 9215, 9214, 9213, 9212, 9386, 9383, 8610, 9104, 8607, 8606, 8604, 13994, 9179, 8806, 16912, 9919, 8694, 8695, 8696, 8697, 8698, 9190, 8803, 8804, 8617, 9180, 8917, 9025, 8577, 9388, 9586, 13983, 10166, 14183, 11828, 14166, 14170, 14175, 13938, 13984, 14008, 13982, 16486, 13899, 10160, 10169, 10159);

                                     QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on katello_content_facet_errata  (cost=337.88..10744.72 rows=16127 width=12) (actual time=0.816..2.048 ro
ws=3310 loops=1)
   Recheck Cond: (content_facet_id = ANY ('{8984,9436,9384,9215,9214,9213,9212,9386,9383,8610,9104,8607,8606,8604,13994,917
9,8806,16912,9919,8694,8695,8696,8697,8698,9190,8803,8804,8617,9180,8917,9025,8577,9388,9586,13983,10166,14183,11828,14166,
14170,14175,13938,13984,14008,13982,16486,13899,10160,10169,10159}'::integer[]))
   ->  Bitmap Index Scan on katello_content_facet_errata_caid  (cost=0.00..333.84 rows=16127 width=0) (actual time=0.791..0
.791 rows=3310 loops=1)
         Index Cond: (content_facet_id = ANY ('{8984,9436,9384,9215,9214,9213,9212,9386,9383,8610,9104,8607,8606,8604,13994
,9179,8806,16912,9919,8694,8695,8696,8697,8698,9190,8803,8804,8617,9180,8917,9025,8577,9388,9586,13983,10166,14183,11828,14
166,14170,14175,13938,13984,14008,13982,16486,13899,10160,10169,10159}'::integer[]))
 Total runtime: 2.312 ms
(5 rows)</div>

Comment 2 Andrew Kofink 2017-10-10 16:59:40 UTC
Created redmine issue http://projects.theforeman.org/issues/21282 from this bug

Comment 4 Tomer Brisker 2018-01-02 07:48:18 UTC
*** Bug 1518712 has been marked as a duplicate of this bug. ***

Comment 5 pm-sat@redhat.com 2018-02-21 16:54:37 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.
> > 
> > https://access.redhat.com/errata/RHSA-2018:0336


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