Bug #25279
closedMissing index on lookup_values table
Description
Cloned from https://bugzilla.redhat.com/show_bug.cgi?id=1641140
foreman=# explain analyse SELECT "lookup_keys"."id" AS t0_r0, "lookup_keys"."key" AS t0_r1, "lookup_keys"."created_at" AS t0_r2, "lookup_keys"."updated_at" AS t0_r3, "lookup_keys"."puppetclass_id" AS t0_r4, "lookup_keys"."default_value" AS t0_r5, "lookup_keys"."path" AS t0_r6, "lookup_keys"."description" AS t0_r7, "lookup_keys"."validator_type" AS t0_r8, "lookup_keys"."validator_rule" AS t0_r9, "lookup_keys"."key_type" AS t0_r10, "lookup_keys"."override" AS t0_r11, "lookup_keys"."required" AS t0_r12, "lookup_keys"."merge_overrides" AS t0_r13, "lookup_keys"."avoid_duplicates" AS t0_r14, "lookup_keys"."omit" AS t0_r15, "lookup_keys"."type" AS t0_r16, "lookup_keys"."merge_default" AS t0_r17, "lookup_keys"."hidden_value" AS t0_r18, "environment_classes"."puppetclass_id" AS t1_r0, "environment_classes"."environment_id" AS t1_r1, "environment_classes"."id" AS t1_r2, "environment_classes"."puppetclass_lookup_key_id" AS t1_r3 FROM "lookup_keys" INNER JOIN "environment_classes" ON "environment_classes"."puppetclass_lookup_key_id" = "lookup_keys"."id" INNER JOIN "lookup_values" ON "lookup_values"."lookup_key_id" = "lookup_keys"."id" WHERE "lookup_keys"."type" IN ('PuppetclassLookupKey') AND "lookup_keys"."override" = 't' AND "environment_classes"."environment_id" = '6' AND "environment_classes"."puppetclass_id" IN (34, 43, 25, 32, 8, 58, 1, 10, 59, 16, 39, 192, 6, 40, 53, 29, 2, 159, 3, 47, 51, 41, 31, 35, 61, 5, 13, 44, 49, 301, 7, 37, 14, 45, 46, 27, 48, 17, 28, 36, 15, 38, 4, 160, 60, 50) ORDER BY lookup_keys.key;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Sort (cost=970.79..970.94 rows=62 width=833) (actual time=77.102..78.308 rows=8045 loops=1)
Sort Key: lookup_keys.key
Sort Method: quicksort Memory: 8765kB
-> Hash Join (cost=659.37..968.94 rows=62 width=833) (actual time=24.904..32.019 rows=8045 loops=1)
Hash Cond: (environment_classes.puppetclass_lookup_key_id = lookup_keys.id)
-> Bitmap Heap Scan on environment_classes (cost=10.40..318.47 rows=44 width=16) (actual time=0.074..0.370
rows=115 loops=1)
Recheck Cond: (environment_id = 6)
Filter: (puppetclass_id = ANY ('{34,43,25,32,8,58,1,10,59,16,39,192,6,40,53,29,2,159,3,47,51,41,31,35,
61,5,13,44,49,301,7,37,14,45,46,27,48,17,28,36,15,38,4,160,60,50}'::integer[]))
Rows Removed by Filter: 247
-> Bitmap Index Scan on index_environment_classes_on_environment_id (cost=0.00..10.39 rows=284 width
=0) (actual time=0.057..0.057 rows=364 loops=1)
Index Cond: (environment_id = 6)
-> Hash (cost=616.53..616.53 rows=2595 width=821) (actual time=24.811..24.811 rows=9643 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8166kB
-> Hash Join (cost=38.97..616.53 rows=2595 width=821) (actual time=0.657..10.940 rows=9643 loops=1)
Hash Cond: (lookup_values.lookup_key_id = lookup_keys.id)
-> Seq Scan on lookup_values (cost=0.00..515.44 rows=9644 width=4) (actual time=0.009..2.051 r
ows=9644 loops=1)
-> Hash (cost=35.80..35.80 rows=254 width=817) (actual time=0.641..0.641 rows=249 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 50kB
-> Seq Scan on lookup_keys (cost=0.00..35.80 rows=254 width=817) (actual time=0.012..0.4
53 rows=249 loops=1)
Filter: (override AND ((type)::text = 'PuppetclassLookupKey'::text))
Rows Removed by Filter: 695
Total runtime: 84.515 ms
(22 rows)
foreman=# create index index_lookup_values_lookup_key_id on lookup_values (lookup_key_id);
CREATE INDEX
foreman=# explain analyse SELECT "lookup_keys"."id" AS t0_r0, "lookup_keys"."key" AS t0_r1, "lookup_keys"."created_at" AS t0_r2, "lookup_keys"."updated_at" AS t0_r3, "lookup_keys"."puppetclass_id" AS t0_r4, "lookup_keys"."default_value" AS t0_r5, "lookup_keys"."path" AS t0_r6, "lookup_keys"."description" AS t0_r7, "lookup_keys"."validator_type" AS t0_r8, "lookup_keys"."validator_rule" AS t0_r9, "lookup_keys"."key_type" AS t0_r10, "lookup_keys"."override" AS t0_r11, "lookup_keys"."required" AS t0_r12, "lookup_keys"."merge_overrides" AS t0_r13, "lookup_keys"."avoid_duplicates" AS t0_r14, "lookup_keys"."omit" AS t0_r15, "lookup_keys"."type" AS t0_r16, "lookup_keys"."merge_default" AS t0_r17, "lookup_keys"."hidden_value" AS t0_r18, "environment_classes"."puppetclass_id" AS t1_r0, "environment_classes"."environment_id" AS t1_r1, "environment_classes"."id" AS t1_r2, "environment_classes"."puppetclass_lookup_key_id" AS t1_r3 FROM "lookup_keys" INNER JOIN "environment_classes" ON "environment_classes"."puppetclass_lookup_key_id" = "lookup_keys"."id" INNER JOIN "lookup_values" ON "lookup_values"."lookup_key_id" = "lookup_keys"."id" WHERE "lookup_keys"."type" IN ('PuppetclassLookupKey') AND "lookup_keys"."override" = 't' AND "environment_classes"."environment_id" = '6' AND "environment_classes"."puppetclass_id" IN (34, 43, 25, 32, 8, 58, 1, 10, 59, 16, 39, 192, 6, 40, 53, 29, 2, 159, 3, 47, 51, 41, 31, 35, 61, 5, 13, 44, 49, 301, 7, 37, 14, 45, 46, 27, 48, 17, 28, 36, 15, 38, 4, 160, 60, 50) ORDER BY lookup_keys.key;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
Sort (cost=405.82..405.98 rows=62 width=833) (actual time=26.527..27.464 rows=8045 loops=1)
Sort Key: lookup_keys.key
Sort Method: quicksort Memory: 8765kB
-> Nested Loop (cost=49.37..403.98 rows=62 width=833) (actual time=0.770..5.552 rows=8045 loops=1)
Join Filter: (environment_classes.puppetclass_lookup_key_id = lookup_values.lookup_key_id)
-> Hash Join (cost=49.37..357.69 rows=8 width=833) (actual time=0.723..1.076 rows=58 loops=1)
Hash Cond: (environment_classes.puppetclass_lookup_key_id = lookup_keys.id)
-> Bitmap Heap Scan on environment_classes (cost=10.40..318.47 rows=44 width=16) (actual time=0.064.
.0.336 rows=115 loops=1)
Recheck Cond: (environment_id = 6)
Filter: (puppetclass_id = ANY ('{34,43,25,32,8,58,1,10,59,16,39,192,6,40,53,29,2,159,3,47,51,41,
31,35,61,5,13,44,49,301,7,37,14,45,46,27,48,17,28,36,15,38,4,160,60,50}'::integer[]))
Rows Removed by Filter: 247
-> Bitmap Index Scan on index_environment_classes_on_environment_id (cost=0.00..10.39 rows=284
width=0) (actual time=0.050..0.050 rows=364 loops=1)
Index Cond: (environment_id = 6)
-> Hash (cost=35.80..35.80 rows=254 width=817) (actual time=0.648..0.648 rows=249 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 50kB
-> Seq Scan on lookup_keys (cost=0.00..35.80 rows=254 width=817) (actual time=0.012..0.451 row
s=249 loops=1)
Filter: (override AND ((type)::text = 'PuppetclassLookupKey'::text))
Rows Removed by Filter: 695
-> Index Only Scan using index_lookup_values_lookup_key_id on lookup_values (cost=0.00..4.72 rows=85 width
=4) (actual time=0.005..0.047 rows=139 loops=58)
Index Cond: (lookup_key_id = lookup_keys.id)
Heap Fetches: 2706
Total runtime: 28.669 ms
(22 rows)
Updated by Lukas Zapletal over 6 years ago
- Subject changed from Missing index on lookup_values table to Missing index on lookup_values table
- Difficulty set to easy
- Triaged changed from No to Yes
Updated by The Foreman Bot over 6 years ago
- Status changed from New to Ready For Testing
- Pull request https://github.com/theforeman/foreman/pull/6193 added
Updated by Tomer Brisker over 6 years ago
- Category changed from PuppetCA to Database
- Fixed in Releases 1.21.0 added
Updated by Aditi Puntambekar over 6 years ago
- Status changed from Ready For Testing to Closed
Applied in changeset 0097950749823e70360c21cf7c701afed9cb8f2b.