Bug 1516244

So, We tried to run EXPLAIN ANALYZE on the above SQL and seems like the Postgres query planner is running a Sequential Scan for getting the results of the data.

x----snip----x
Seq Scan on katello_subscription_facets  (cost=0.00..1128500.62 rows=18995 width=4) (actual time=0.366..10567.707 rows=29902 loops=1)
x----snip----x

The planner shows a runtime of more than 10s to execute the query which is quite too long for a query to execute and makes it very inefficient.

If you run this sql against the foreman db, does the issue disappear?

create index test_uuid_idx on katello_subscription_facets(uuid);

Created attachment 1359361 [details]
Full query

Created attachment 1359362 [details]
`time psql foreman --file /tmp/mine.sql > out2.log` with "EXPLAIN ANALYZE ..."

-bash-4.2$ time psql foreman --file /tmp/mine.sql > out2.log

real	0m0.505s
user	0m0.089s
sys	0m0.015s

Created attachment 1359363 [details]
`time psql foreman --file /tmp/mine.sql > out3.log` only as "SELECT ..."

-bash-4.2$ time psql foreman --file /tmp/mine.sql > out3.log

real	0m0.344s
user	0m0.023s
sys	0m0.010s

(In reply to Chris Duryee from comment #2)
> If you run this sql against the foreman db, does the issue disappear?
> 
> create index test_uuid_idx on katello_subscription_facets(uuid);

Hello Chris,

Thanks for the suggestion, this seems to be working out well for the Query execution time as noted by jhutar. :)

Created redmine issue http://projects.theforeman.org/issues/21928 from this bug

Moving this bug to POST for triage into Satellite 6 since the upstream issue http://projects.theforeman.org/issues/21928 has been resolved.

Verified in Satellite 6.3 Snap 35.

Added over 25k content hosts to my satellite. Then registered on additional host while monitoring the postgres daily log, with debugging enabled. I also verified that the index was added to katello_subscription_facets. See below for results.

The new query performance time is now less than a second!

foreman=# \d katello_subscription_facets
                                          Table "public.katello_subscription_facets"
       Column       |            Type             |                                 Modifiers                                  
--------------------+-----------------------------+----------------------------------------------------------------------------
...
 uuid               | character varying(255)      | 
...
Indexes:
...
    "index_katello_subscription_facets_on_uuid" btree (uuid)


foreman=# select count(distinct uuid) from katello_subscription_facets;
 count 
-------
 25054


2018-02-06 22:56:48 CET STATEMENT:  SELECT "katello_subscription_facets"."id" FROM "katello_subscription_facets" WHERE "katello_subscription_facets"."uuid" IN ('a187723b-1cef-471a-920b-7a9a9ec970d3', 'a28d1ce0-a1be-4f50-8c3b-
... 
'f0c7cbb8-d164-4d80-91cb-d2dd3806e1c9')

2018-02-06 22:56:48 CET DEBUG:  "katello_subscription_facets": scanned 394 of 394 pages, containing 26055 live rows and 2009 dead rows; 26055 rows in sample, 26055 estimated total rows

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