Project

General

Profile

Actions

Bug #25278

closed

Missing index on environment_classes table

Added by Marek Hulán over 5 years ago. Updated over 5 years ago.

Status:
Closed
Priority:
High
Category:
Database
Target version:
-
Difficulty:
Triaged:
No
Fixed in Releases:
Found in Releases:

Description

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

foreman=# create index index_environment_classes_on_environment_id_puppetclass_id on environment_classes (environment_id, puppetclass_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;
QU
ERY PLAN

---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
------
Sort (cost=379.22..379.38 rows=62 width=833) (actual time=24.569..25.653 rows=8045 loops=1)
Sort Key: lookup_keys.key
Sort Method: quicksort Memory: 8765kB
-> Nested Loop (cost=211.01..377.38 rows=62 width=833) (actual time=0.655..4.863 rows=8045 loops=1)
Join Filter: (environment_classes.puppetclass_lookup_key_id = lookup_values.lookup_key_id)
-> Hash Join (cost=211.01..331.10 rows=8 width=833) (actual time=0.646..0.747 rows=58 loops=1)
Hash Cond: (environment_classes.puppetclass_lookup_key_id = lookup_keys.id)
-> Bitmap Heap Scan on environment_classes (cost=172.03..291.88 rows=44 width=16) (actual time=0.073
..0.112 rows=115 loops=1)
Recheck Cond: ((environment_id = 6) AND (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[]))
)
-> Bitmap Index Scan on index_environment_classes_on_environment_id_puppetclass_id (cost=0.00.
.172.02 rows=44 width=0) (actual time=0.066..0.066 rows=115 loops=1)
Index Cond: ((environment_id = 6) AND (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
[])))
-> Hash (cost=35.80..35.80 rows=254 width=817) (actual time=0.563..0.563 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.013..0.401 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.003..0.042 rows=139 loops=58)
Index Cond: (lookup_key_id = lookup_keys.id)
Heap Fetches: 2706
Total runtime: 27.865 ms
(20 rows)

foreman=#

Actions #1

Updated by The Foreman Bot over 5 years ago

  • Status changed from New to Ready For Testing
  • Assignee set to Lukas Zapletal
  • Pull request https://github.com/theforeman/foreman/pull/6165 added
Actions #2

Updated by Tomer Brisker over 5 years ago

  • Subject changed from Missing index on environment_classes table to Missing index on environment_classes table
  • Category changed from PuppetCA to Database
  • Fixed in Releases 1.21.0 added
Actions #3

Updated by Lukas Zapletal over 5 years ago

  • Status changed from Ready For Testing to Closed
Actions

Also available in: Atom PDF