Plugins » Katello

Cloned from https://bugzilla.redhat.com/show_bug.cgi?id=1486411

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>