Skip to content

Search improvements

Choose a tag to compare

View all tags
@dlangille dlangille released this 16 Oct 13:30
· 25 commits to main since this release
2.16.4
631ffa0

What's changed

Performance

  • Search no longer walks every commit on the branch. The search queries joined system_branch by name, so PostgreSQL guessed about 10,000 head rows in commit_log_branches instead of 568,000, and checked every one of them: 10 seconds for a "depends on this port" search that matches one port. The branch id is now looked up first and passed as a literal (SystemBranch::getBranchID()), in both the count and the results query. (7008147)
  • Category pages no longer compute pkg_plist() for every port. It cost about 1 second per page of 100 ports, and the listing never shows it. (8a10af6)

Fixes

  • Sample LIB_DEPENDS lines are back on port pages. Since 2022, the value from pkg_plist() was discarded by an IsSet() check on the wrong variable, so the lines never appeared. (8a10af6)

Search gate (#692)

  • The gate no longer trusts the Referer header. Bots were setting it to a FreshPorts page to get past the gate. A search now needs a login or a valid search_ok cookie. (631ffa0)
  • www/search-gate.php now also shows the challenge page on a GET. It needs no database, so nginx can send cookie-less searches there before PHP or PostgreSQL is involved. With the gate off, it returns 401 instead of redirecting, so it can't loop with nginx. (631ffa0)

Upgrade notes

Run these database changes before deploying the code. The code works without them, but slowly, and port pages will call the old, expensive pkg_plist().

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY ports_depends_build_trgm_idx      ON ports USING gin (depends_build     gin_trgm_ops);
CREATE INDEX CONCURRENTLY ports_depends_lib_trgm_idx        ON ports USING gin (depends_lib       gin_trgm_ops);
CREATE INDEX CONCURRENTLY ports_depends_run_trgm_idx        ON ports USING gin (depends_run       gin_trgm_ops);
CREATE INDEX CONCURRENTLY ports_short_description_trgm_idx  ON ports USING gin (short_description gin_trgm_ops);
ANALYZE ports;

CREATE INDEX CONCURRENTLY generate_plist_port_id_libs_idx
    ON generate_plist (port_id) INCLUDE (installed_file)
 WHERE installed_file ~ 'lib/[^/]*?\.so';

CREATE OR REPLACE FUNCTION public.pkg_plist(bigint)
 RETURNS json
 LANGUAGE sql
 STABLE
AS $function$
   WITH tmp AS (
       select regexp_match(installed_file, 'lib/[^/]*?\.so') as lib
         from generate_plist
        where generate_plist.port_id = $1
          and installed_file ~ 'lib/[^/]*?\.so'
   )
   select json_agg(distinct lib[1]) from tmp where lib is not null
$function$;

CREATE INDEX CONCURRENTLY IF NOT EXISTS port_dependencies_upon_type_idx
    ON port_dependencies (port_id_dependent_upon, dependency_type, port_id);
VACUUM (ANALYZE) port_dependencies;
  • Running them: CREATE INDEX CONCURRENTLY doesn't lock the table, and can't run inside a transaction.
  • The pkg_plist() function: its WHERE must match the partial index's condition exactly, or the index isn't used.
  • port_dependencies_upon_type_idx: this isn't tied to a commit, but it speeds up every port page (about 50 ms → under 1 ms per dependency lookup). It's already on production.
  • Optional: ALTER ROLE <website role> SET jit = off;. That saves about 50 ms of JIT compilation on larger queries.

Search gate: nothing changes until SEARCH_GATE_SECRET is set in include/constants.local.php. The matching nginx configuration is separate from this release.

Full Changelog: 2.16.3...2.16.4

Assets 2
Loading