Package: qa.debian.org
Severity: wishlist

We already have all paths in the DB, it would trivial and very useful to
offer file/path-based search functionalities.

The only caveat is query execution team.
Some preliminary micro-benchmarks:

- it is too slow without decent index:

   debsources=> select count(*) from files where path like '%stdio%';
   count
  -------
    9643
  (1 row)

  Time: 7736,861 ms

- but it works pretty decently with trigram indexing:

  debsources=> \d ix_files_path_trgm
        Index "public.ix_files_path_trgm"
   Column |  Type   |          Definition
  --------+---------+------------------------------
   encode | integer | encode(path, 'escape'::text)
  gin, for table "public.files"

  debsources=> select count(*) from files where encode(path, 'escape') like '%stdio%';
   count
  -------
    9643
  (1 row)

  Time: 77,558 ms

  To work, the above requires CREATE EXTENSION 'trgm';


Cheers.