Debugging PostgreSQL performance issues

[ralf-berger]

To help debugging issue #1212, the following pgAdmin UI are (temporarily) available:

• https://pgadmin.coursemapper.soco.inko.cloud/browser/
• https://pgadmin.edge.coursemapper.soco.inko.cloud/browser/

I'll try to find out what causes the high load, but I'm not exactly an expert in PostgreSQL. @rawaa123 Please let me know, if you need any particular extensions/settings for performance profiling.

[ralf-berger]

I tried to dig a bit into the whole performance situation and used the pg_stat_statements extension to check which queries take up the most time:

SELECT calls, round(total_exec_time) AS total_exec, round(mean_exec_time) as mean_exec, round(jit_generation_time) as jit_generation, round(jit_inlining_time) as jit_inlining, round(jit_emission_time) as jit_emission, jit_optimization_count, query FROM public.pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 7

After a short while this is the timing information (in milliseconds) I got:

calls	total_exec	mean_exec	jit_generation	jit_inlining	jit_emission	jit_optimization_count	query
 1745	  66069807	    37862	           932	       44954	       12723	                  1745	SELECT * FROM page_categories WHERE page_title = $1
   99	    113867	     1150	             0	           0	           0	                     0	SELECT title FROM disambiguations WHERE refers_to = $1
  120	     15171	      126	             0	           0	           0	                     0	SELECT title, embedding FROM embeddings WHERE title = ANY($1)
 2043	     12389	        6	             0	           0	           0	                     0	SELECT title, abstract, links FROM pages WHERE title = $1
  587	      5335	        9	             0	           0	           0	                     0	SELECT pages.title, pages.abstract, pages.links FROM pages JOIN redirects ON pages.title = redirects.redirect_to WHERE redirects.title = $1
   99	       477	        5	             0	           0	           0	                     0	SELECT refers_to FROM disambiguations WHERE title = $1
   49	       315	        6	             0	           0	           0	                     0	SELECT refers_to FROM disambiguations WHERE title = ANY($1)

[ralf-berger]

So the only statement that causes problems seems to be text searches on page_categories.page_title. The preprocessing seems to create an index covering this colum:

CourseMapper-webserver/coursemapper-kg/preprocess/src/database.py

Lines 44 to 45 in 1092da6

	cur.execute(
	'CREATE INDEX page_categories_page_title ON page_categories (page_title)')

[ralf-berger]

In development SELECT indexname FROM pg_indexes WHERE tablename = 'page_categories' yields:

"page_categories_pkey"
"unique_page_categories"
"page_categories_category_name"
"page_categories_page_title"

In production SELECT indexname FROM pg_indexes WHERE tablename = 'page_categories' yields:

"page_categories_pkey"

So the only index present in production is the primary key.