[Spacewalk-list] Postgres spacewalk

[Jan Pazdziora]

On Thu, Nov 03, 2011 at 03:40:30PM +0000, John Hodrien wrote:
> 
> An example of a query that takes far too long.  I'm not in any way a DBA, and
> really do not have a good understanding of postgresql so I'm going to be
> suitably vague here, because I can't really manage anything else.

Actually, you are dead on the cause of the problem.

In-depth investigation of one particular query is the way to go.
In this case, we would expect the

[...]

> spaceschema-#                            and cf.config_file_name_id = lookup_config_filename(E'/var/lib/sss/db/cache_default.ldb')

to use index on the rhnConfigFile (cf) table, yet it does not happen:

>                                              ->  Seq Scan on rhnconfigfile cf  (cost=0.00..28.25 rows=1 width=29) (actual time=0.544..2.820 rows=1 loops=1)
>                                                    Filter: (config_file_name_id = lookup_config_filename('/var/lib/sss/db/cache_default.ldb'::character varying))

I believe the cause of the problem is that the functions (including
the lookup_* functions) are volatile by default in PostgreSQL and
PostgreSQL does not seem to be willing to use them for index lookups.
We will need to make them stable, or even immutable.

Can you rewrite the lookup_config_filename to be stable, to see
if it makes a difference?

-- 
Jan Pazdziora
Principal Software Engineer, Satellite Engineering, Red Hat