Hacktoberfest 2026: los issues que los mantenedores marcaron para octubre, abiertos y aptos para principiantes. Explorar issues de Hacktoberfest

pg.tables_without_index_use false-positives on small, correctly-indexed tables

Abierto
  • ¿Algo no está bien?
#1,157 1 comentario 10 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 1 día

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
55/100
Tipo de issue
Error
Claridad
Bastante claro
Estado de actividad
Activo
Stack tecnológico
php, postgresql
Área
databases, observability

Línea de trabajo

The rule is in apps/serverinfo/lib/Database/RuleSet/Postgres.php; read its seq_scan/idx_scan check and then inspect Advisor.php's MIN_UPTIME_HOURS/COUNTER_SENSITIVE_RULE_IDS gate. Compare the proposed size or row-count signals against the reported PostgreSQL behavior, and confirm done by ensuring genuinely small indexed tables are no longer reported while large sequentially scanned tables remain visible.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

Nextcloud & app version

  • Nextcloud Server: 35.0.0
  • serverinfo app: 7.0.0
  • Database: PostgreSQL 15.18 (Debian 15.18-0+deb12u1)

Summary

The "Database checks" advisor on Settings → Overview → Database flags "Tables scanning sequentially" (pg.tables_without_index_use) as failing for tables that are only a few kilobytes / a handful of rows, already carry a primary-key index, and for which PostgreSQL's own planner correctly and deliberately chooses a sequential scan over an index scan — because the table is small enough that a seq scan is the cheaper plan.

The rule (apps/serverinfo/lib/Database/RuleSet/Postgres.php) triggers purely on:

seq_scan > 1_000_000 AND idx_scan = 0

This threshold only measures scan count, never table size or row count. Since Nextcloud's own small system/config tables (e.g. oc_users, oc_accounts, oc_preferences) are read on effectively every request, their seq_scan counter climbs into the millions over normal server uptime even though each individual scan touches a single 8 KB heap page. The rule has no way to distinguish "1M scans over 2 rows" (free) from "1M scans over 2M rows" (a real missing-index problem), so it reliably false-positives on any long-running, low-traffic instance.

Steps to reproduce

  1. Run a Nextcloud 35 instance on PostgreSQL for long enough that core tables accumulate >1M sequential scans (any small/medium instance left running for a few weeks will do this — these tables are queried on essentially every authenticated request).
  2. Open Settings → Overview. The "Database configuration" check shows a warning.
  3. Open Settings → Administration → Database (or the servrinfo #database panel) → Database checks → "S problémy" (Failing). See "Tables scanning sequentially", listing oc_users, oc_accounts, oc_preferences, oc_mimetypes, oc_guests_users, oc_circles_membership, oc_deck_boards (exact set depends on the instance, but core low-cardinality tables are the reliable offenders).

Evidence that this is a false positive

Actual state on our instance (2 real user accounts, small install):

table live rows total size seq_scan idx_scan
oc_users 3 40 kB 2,198,160 0
oc_preferences 89 96 kB 2,197,028 0
oc_guests_users 0 32 kB 1,782,632 0
oc_mimetypes 240 88 kB 1,709,915 0
oc_accounts 3 72 kB 1,658,128 0
oc_circles_membership 0 64 kB 1,189,503 0
oc_deck_boards 0 56 kB 1,001,275 0

Indexes already exist and are correct per Nextcloud's own schema (occ db:add-missing-indices, occ db:add-missing-primary-keys, occ db:add-missing-columns all report nothing missing):

$ psql -c "SELECT indexname, indexdef FROM pg_indexes WHERE tablename IN ('oc_users','oc_accounts');"
       indexname       |                                   indexdef
------------------------+------------------------------------------------------------------------------
 oc_accounts_pkey       | CREATE UNIQUE INDEX oc_accounts_pkey ON public.oc_accounts USING btree (uid)
 oc_users_pkey          | CREATE UNIQUE INDEX oc_users_pkey ON public.oc_users USING btree (uid)
 user_uid_lower         | CREATE INDEX user_uid_lower ON public.oc_users USING btree (uid_lower)

And PostgreSQL's planner, with that index available, deliberately picks the sequential scan anyway because it's the actually-cheaper plan for a single-page table:

$ psql -c "EXPLAIN SELECT * FROM oc_users WHERE uid = 'h0nyik';"
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on oc_users  (cost=0.00..1.02 rows=1 width=954)
   Filter: ((uid)::text = 'h0nyik'::text)

$ psql -c "EXPLAIN SELECT * FROM oc_accounts WHERE uid = 'h0nyik';"
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on oc_accounts  (cost=0.00..3.08 rows=1 width=178)
   Filter: ((uid)::text = 'h0nyik'::text)

So the rule's own suggested remedy ("Add appropriate indexes to the affected tables") would not change anything: the index is already there, and the planner already knows about it, and correctly ignores it.

Expected behavior

The rule shouldn't fire for tables where a sequential scan is genuinely the optimal plan. A few ways to fix this that seem consistent with the codebase's existing patterns:

  1. Gate on table size, analogous to the existing MIN_UPTIME_HOURS / COUNTER_SENSITIVE_RULE_IDS gate in Advisor.php that already exists to avoid false positives from counters that haven't accumulated meaningful data yet (restart-sensitive ratios). E.g. only flag a table if pg_total_relation_size(relid) (or n_live_tup) is above some minimum (a table under, say, a few hundred KB / a few thousand rows essentially can't benefit from an index scan on PostgreSQL).
  2. Alternatively, cross-check against pg_stat_user_tables.seq_tup_read / seq_scan (average rows read per scan) — a consistently tiny average strongly indicates the table is trivially small, regardless of the raw scan count.
  3. At minimum, mention table size next to each flagged table name in the details so an admin can immediately tell a real missing-index case from a "this table is 40 kB, ignore" case, without having to run their own pg_stat_user_tables query to figure that out (as I had to here).

Additional note

There currently appears to be no way for an admin to acknowledge/dismiss one specific rule result once it's understood to be a non-issue for their instance — every page load re-evaluates and re-flags it (checked Advisor.php, CheckSummary.php, DatabaseController.php, and the core SetupCheckManager — no skip/ignore/acknowledge mechanism beyond the automatic "required value missing" / uptime-gate skips). Even if the size-based fix above isn't adopted, a persistent per-instance "don't show this rule again" would at least let an admin who has verified a result is a non-issue stop seeing it flagged as failing on every visit.

Happy to provide any more detail (pg_stat_user_tables dumps, full EXPLAIN ANALYZE, etc.) if useful.

Lenguaje dominante
JavaScript
Estrellas
133
Forks
67
Merge medio
22 h 55 min
PR fusionados (30 d)
53

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de nextcloud/serverinfo

Todos los issues de nextcloud/serverinfo

Issues similares

Más issues de JavaScript

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.