FreshPorts News

This idea came to me today from swills:

stupid question, would it be possible to make the urls on freshports case insensitive?

so that like https://www.freshports.org/x11/libx11 would go to https://www.freshports.org/x11/libX11

After confirming this was for manually typed URLs (as opposed to generated links), I started looking into it. This is what I found.

In this post, we’re going to look at some old PHP database code and the PostgreSQL stored procedures behind them. I’ll show you the multi-line stored procedure now in use and how a single query will now do the same thing. This change isn’t some clever coding or another solution. The database has evolved and the procedures did not have to. Until now.

Going from sysutils/py-mqttwarn to a database entry

I will base all my links to code on the 1.50.5 release.

If the Nginx webserver cannot follow the URL to a file on disk, it invokes www/–/index.php via this bit of declartion:

  location / {
    try_files $uri $uri/ @missing;
  }

  location @missing {
    rewrite ^ /--/index.php break;

    fastcgi_split_path_info ^(.+\.php)(/.+)$;
    fastcgi_pass unix:/var/run/php-fpm.sock;
    fastcgi_index index.php;
    fastcgi_param SCRIPT_FILENAME $request_filename;
    include fastcgi_params;
  }

Line 2 says: try finding the URL using files on disk and if you file, go do what @missing points to.

Line 6 say: invoke /–/index.php for things you don’t find in a file.

Why /–/index.php? I wanted to put this file somewhere that it would be highly unlikely to collide with anything in the FreeBSD repo (with regards to directories and file names).

The code checks for other special stuff ((badges, scripts, API, etc) before invoking freshports_Parse404URI() on line 148. I will discuss that soon, but first: if nothing else is picked up by /–/index.php, then it’s time to invoke rewrite/missing.php

freshports_Parse404URI() is declared in rewrite/functions.php and the code relevant to our journey is on line 70:

if ($ElementRecord->FetchByName('/ports/head/' . $pathname)) {

Ignoring that hardcoded pathname for now, this function gets passed something like this:

/ports/head/sysutils/py-mqttwarn

There might be more on the path: branch name, commit number, etc.

FetchByName() is located on line 38 of classes/element_record

Now we are getting into the oldest code of FreshPorts. I’m sure this stuff hasn’t changed in over 15 years.

The database function elementGet() is invoked which looks like this:

CREATE OR REPLACE FUNCTION elementGet (text) RETURNS SETOF element_type AS $$
   SELECT id,
          name::text,
          directory_file_flag::text,
          status::text,
          case when IsCategory(Pathname_ID($1)) IS NULL THEN FALSE ELSE TRUE END,
          case when IsPort(    Pathname_ID($1)) IS NULL THEN FALSE ELSE TRUE END,
          element_pathname(id)
     FROM element
    WHERE id = PathName_ID($1);
$$ LANGUAGE SQL STABLE;

The key in this function is PathName_ID() which is used three times and uniquely identifies a single row in the element table.

Element table

The element table is an implementation of a filesystem using a self-referential solution (i.e. it is recursive).

The table (without constraints, references, and triggers) looks like this:

freshports.dev=# \d element
                                     Table "public.element"
       Column        |     Type     | Collation | Nullable |               Default               
---------------------+--------------+-----------+----------+-------------------------------------
 id                  | integer      |           | not null | nextval('element_id_seq'::regclass)
 name                | text         |           | not null | 
 parent_id           | integer      |           |          | 
 directory_file_flag | character(1) |           | not null | 
 status              | character(1) |           | not null | 
Indexes:
    "element_pkey" PRIMARY KEY, btree (id)
    "element_delete" btree (status) WHERE status = 'D'::bpchar
    "element_name" btree (name)
    "element_parent_id" btree (parent_id)

An entry in the top level directory will have parent_id = NULL.

Pathname_ID function

Pathname_ID() is a function which progresses along a supplied pathname from left to right following the each entry from the element table.

CREATE OR REPLACE FUNCTION Pathname_ID(text) returns int4 AS $$

   DECLARE
      in_element_pathname ALIAS FOR $1;

      debug_str         text;
      element_id        int4;
      element_parent_id int4;
      element_name      text;
      element_pathname  text;
      position          int4;
      slash             text;

begin
  slash            := '/';
  element_pathname := in_element_pathname;
  element_id       := -1;

  IF substr(element_pathname,1,1) = slash THEN
    element_pathname := substr(element_pathname, 2);
  END IF;

  position := strpos(element_pathname, slash);
  IF position = 0 THEN
    element_name     := element_pathname;
    element_pathname := '';
  ELSE
    element_name     := substr(element_pathname, 1, position-1);
    element_pathname := substr(element_pathname, position+1);
  END IF;

  select id 
    into element_parent_id
    from element 
   where element.name = element_name
     and parent_id is null;

  debug_str := debug_str || '1 = ' || element_name;

  WHILE(char_length(element_pathname) > 0) LOOP
    position := strpos(element_pathname, slash);
    if position = 0 then
       element_name     := element_pathname;
       element_pathname := '';
    else
       element_name     := substr(element_pathname, 1, position-1);
       element_pathname := substr(element_pathname, position+1);
    end if;

    debug_str := debug_str || '1 = ' || element_name;
    select id 
      into element_parent_id
      from element 
     where element.name = element_name 
       and parent_id    = element_parent_id;

    debug_str := debug_str || ' 2 = ' || element_name;
    debug_str := debug_str || ' 3 = ' || element_parent_id;
  end loop;

  return element_parent_id;
END;
$$  LANGUAGE 'plpgsql' STABLE;

The key lines are 35 and 54. To meet the goal of a case insensitive match, those operators would need to be changed from = to a case insensitive comparison, such as:

where lower(element.name) = lower(element_name)

I do not want to alter this code which is so far down the tree. Instead, I will create a new function which will take a different approach. It will also take advantage of new database features which did not exist 8 years ago, let along 15+ years ago.

Testing new queries

I did some playing around with queries today, looking to find the element_id for a given port, in our example sysutils/py-mqttwarn.

I started off with this, show me things named x11:

freshports.stage=# select *, element_pathname(id) from element where lower(name) = lower('X11');
   id    | name | parent_id | directory_file_flag | status |                    element_pathname                    
---------+------+-----------+---------------------+--------+--------------------------------------------------------
   43426 | x11  |     33542 | F                   | A      | /ports/head/misc/kdeutils11/x11
   52955 | x11  |      4760 | F                   | A      | /ports/head/misc/kdeutils2/x11
   58887 | x11  |     50378 | F                   | A      | /ports/head/textproc/xml-i18n-tools/x11
   58962 | x11  |      3582 | F                   | A      | /ports/head/www/gtkhtml/x11
   83338 | x11  |     77344 | D                   | A      | /doc/head/en_US.ISO8859-1/books/handbook/x11
  104943 | x11  |    104814 | D                   | A      | /doc/head/fr_FR.ISO8859-1/books/handbook/x11
   99879 | x11  |     86040 | D                   | A      | /doc/head/ja_JP.eucJP/books/handbook/x11
  200645 | x11  |    200550 | D                   | A      | /doc/head/nl_NL.ISO8859-1/books/handbook/x11
  110618 | x11  |     77691 | D                   | A      | /doc/head/de_DE.ISO8859-1/books/handbook/x11
  112293 | x11  |    112148 | D                   | A      | /doc/head/it_IT.ISO8859-15/books/handbook/x11
  113146 | X11  |     77283 | D                   | A      | /base/head/release/scripts/X11
  200746 | x11  |    200655 | D                   | A      | /doc/head/zh_CN.GB2312/books/handbook/x11
  171234 | x11  |    110131 | D                   | A      | /doc/head/ru_RU.KOI8-R/books/handbook/x11
  271292 | x11  |    271177 | D                   | A      | /doc/head/mn_MN.UTF-8/books/handbook/x11
  200939 | x11  |    110120 | D                   | A      | /doc/head/es_ES.ISO8859-1/books/handbook/x11
  234867 | x11  |    234757 | D                   | A      | /doc/head/zh_TW.Big5/books/handbook/x11
  255132 | x11  |    255022 | D                   | A      | /doc/head/pl_PL.ISO8859-2/books/handbook/x11
  288692 | x11  |    288608 | D                   | A      | /doc/head/el_GR.ISO8859-7/books/handbook/x11
  300947 | x11  |    300813 | D                   | A      | /doc/head/hu_HU.ISO8859-2/books/handbook/x11
  433144 | x11  |    433041 | D                   | A      | /ru_RU.KOI8-R/books/handbook/x11
  433549 | x11  |    433544 | D                   | A      | /mn_MN.UTF-8/books/handbook/x11
  434309 | x11  |    433663 | D                   | A      | /ja_JP.eucJP/books/handbook/x11
  439412 | x11  |    432543 | D                   | A      | /en_US.ISO8859-1/books/handbook/x11
  439459 | x11  |    433298 | D                   | A      | /nl_NL.ISO8859-1/books/handbook/x11
  457198 | x11  |    111733 | D                   | A      | /doc/head/pt_BR.ISO8859-1/books/handbook/x11
     231 | x11  |    464087 | D                   | A      | /ports/head/x11
  465674 | x11  |    465603 | D                   | A      | /doc/translations/nl_NL.ISO8859-1/books/handbook/x11
  501886 | x11  |    465481 | D                   | A      | /doc/translations/en_US.ISO8859-1/books/handbook/x11
  537516 | x11  |    537439 | D                   | A      | /doc/head/zh_CN.UTF-8/books/handbook/x11
  663947 | x11  |    663946 | D                   | A      | /doc/release/10.2.0/en_US.ISO8859-1/books/handbook/x11
  587542 | x11  |    587471 | D                   | D      | /doc/head/zh_TW.UTF-8/books/handbook/x11
  745067 | x11  |    743628 | D                   | A      | /ports/branches/2016Q4/x11
  770464 | x11  |    763349 | D                   | A      | /ports/branches/2017Q1/x11
  792524 | x11  |    790218 | D                   | A      | /ports/branches/2017Q2/x11
  812565 | x11  |    812271 | D                   | A      | /ports/branches/2017Q3/x11
  829995 | x11  |    829562 | D                   | A      | /ports/branches/2017Q4/x11
  870434 | x11  |    867105 | D                   | A      | /ports/branches/2018Q2/x11
  882372 | x11  |    881009 | D                   | A      | /ports/branches/2018Q3/x11
  901665 | x11  |    901631 | D                   | A      | /ports/branches/2018Q4/x11
  914724 | x11  |    914354 | D                   | A      | /ports/branches/2019Q1/x11
  936002 | x11  |    935538 | D                   | A      | /ports/branches/2019Q2/x11
  954616 | x11  |    949697 | D                   | A      | /ports/branches/2019Q3/x11
  971674 | x11  |    961357 | D                   | A      | /ports/branches/2019Q4/x11
  978479 | x11  |    978429 | D                   | A      | /ports/branches/2020Q1/x11
 1014222 | x11  |   1012899 | D                   | A      | /ports/branches/2020Q2/x11
 1055981 | x11  |   1053714 | D                   | A      | /ports/branches/2020Q3/x11
(46 rows)

Based on line 29, show me a child of that element named libX11:

freshports.stage=# select *, element_pathname(id) from element where lower(name) = lower('libX11') and parent_id = 231;
   id   |  name  | parent_id | directory_file_flag | status |    element_pathname    
--------+--------+-----------+---------------------+--------+------------------------
 163623 | libX11 |       231 | D                   | A      | /ports/head/x11/libX11
(1 row)

freshports.stage=# 

That’s the one.

Using this approach, I came up with few queries, which I later abandoned for another solution.

The abandoned queries

I tried this join of four tables:

freshports.stage=# explain analyse
freshports.stage-# SELECT E4.id, element_pathname(E4.id)
freshports.stage-#   FROM element E1, element E2, element E3, element E4
freshports.stage-#  WHERE E1.parent_id IS NULL
freshports.stage-#    AND E2.parent_id = E1.id
freshports.stage-#    AND E3.parent_id = E2.id
freshports.stage-#    AND E4.parent_id = E3.id
freshports.stage-#    AND E2.name = 'head'
freshports.stage-#    AND E1.name = 'ports'
freshports.stage-#    AND lower(E3.name) = lower('sysutils')
freshports.stage-#    AND lower(E4.name) = lower('py-mqttwarn');
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=20.05..74.95 rows=1 width=36) (actual time=1.833..2.299 rows=1 loops=1)
   ->  Nested Loop  (cost=19.62..67.62 rows=1 width=4) (actual time=0.126..0.138 rows=1 loops=1)
         ->  Nested Loop  (cost=19.19..27.23 rows=1 width=4) (actual time=0.061..0.063 rows=1 loops=1)
               ->  Bitmap Heap Scan on element e1  (cost=9.76..13.77 rows=1 width=4) (actual time=0.034..0.035 rows=1 loops=1)
                     Recheck Cond: ((name = 'ports'::text) AND (parent_id IS NULL))
                     Heap Blocks: exact=1
                     ->  BitmapAnd  (cost=9.76..9.76 rows=1 width=0) (actual time=0.030..0.030 rows=0 loops=1)
                           ->  Bitmap Index Scan on element_name  (cost=0.00..4.56 rows=17 width=0) (actual time=0.016..0.016 rows=38 loops=1)
                                 Index Cond: (name = 'ports'::text)
                           ->  Bitmap Index Scan on element_parent_id  (cost=0.00..4.95 rows=70 width=0) (actual time=0.010..0.010 rows=100 loops=1)
                                 Index Cond: (parent_id IS NULL)
               ->  Bitmap Heap Scan on element e2  (cost=9.43..13.45 rows=1 width=8) (actual time=0.023..0.024 rows=1 loops=1)
                     Recheck Cond: ((name = 'head'::text) AND (parent_id = e1.id))
                     Heap Blocks: exact=1
                     ->  BitmapAnd  (cost=9.43..9.43 rows=1 width=0) (actual time=0.020..0.020 rows=0 loops=1)
                           ->  Bitmap Index Scan on element_name  (cost=0.00..4.56 rows=17 width=0) (actual time=0.009..0.009 rows=15 loops=1)
                                 Index Cond: (name = 'head'::text)
                           ->  Bitmap Index Scan on element_parent_id  (cost=0.00..4.62 rows=26 width=0) (actual time=0.008..0.008 rows=5 loops=1)
                                 Index Cond: (parent_id = e1.id)
         ->  Index Scan using element_parent_id on element e3  (cost=0.43..40.38 rows=1 width=8) (actual time=0.064..0.074 rows=1 loops=1)
               Index Cond: (parent_id = e2.id)
               Filter: (lower(name) = 'sysutils'::text)
               Rows Removed by Filter: 88
   ->  Index Scan using element_parent_id on element e4  (cost=0.43..7.07 rows=1 width=8) (actual time=1.540..1.994 rows=1 loops=1)
         Index Cond: (parent_id = e3.id)
         Filter: (lower(name) = 'py-mqttwarn'::text)
         Rows Removed by Filter: 2380
 Planning Time: 0.689 ms
 Execution Time: 2.352 ms
(29 rows)

freshports.stage=# 

I tried rewriting with a JOIN JOIN approach, but that’s really just the previous query:

freshports.stage=# -- find a port which matches this
freshports.stage=# -- case insensitive
freshports.stage=# 
freshports.stage=# 
freshports.stage=# explain analyse
freshports.stage-# SELECT E4.id, element_pathname(E4.id)
freshports.stage-#   FROM element E1
freshports.stage-#   JOIN element E2 ON E1.parent_id IS NULL AND E1.name = 'ports'
freshports.stage-#                  AND E2.parent_id = E1.id AND E2.name = 'head'
freshports.stage-#   JOIN element E3 ON E3.parent_id = E2.id AND lower(E3.name) = lower('sysutils')
freshports.stage-#   JOIN element E4 ON E4.parent_id = E3.id AND lower(E4.name) = lower('bacula9-server');
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=20.05..74.95 rows=1 width=36) (actual time=3.729..4.788 rows=1 loops=1)
   ->  Nested Loop  (cost=19.62..67.62 rows=1 width=4) (actual time=0.278..0.309 rows=1 loops=1)
         ->  Nested Loop  (cost=19.19..27.23 rows=1 width=4) (actual time=0.129..0.132 rows=1 loops=1)
               ->  Bitmap Heap Scan on element e1  (cost=9.76..13.77 rows=1 width=4) (actual time=0.072..0.073 rows=1 loops=1)
                     Recheck Cond: ((name = 'ports'::text) AND (parent_id IS NULL))
                     Heap Blocks: exact=1
                     ->  BitmapAnd  (cost=9.76..9.76 rows=1 width=0) (actual time=0.063..0.063 rows=0 loops=1)
                           ->  Bitmap Index Scan on element_name  (cost=0.00..4.56 rows=17 width=0) (actual time=0.032..0.033 rows=38 loops=1)
                                 Index Cond: (name = 'ports'::text)
                           ->  Bitmap Index Scan on element_parent_id  (cost=0.00..4.95 rows=70 width=0) (actual time=0.023..0.023 rows=100 loops=1)
                                 Index Cond: (parent_id IS NULL)
               ->  Bitmap Heap Scan on element e2  (cost=9.43..13.45 rows=1 width=8) (actual time=0.047..0.049 rows=1 loops=1)
                     Recheck Cond: ((name = 'head'::text) AND (parent_id = e1.id))
                     Heap Blocks: exact=1
                     ->  BitmapAnd  (cost=9.43..9.43 rows=1 width=0) (actual time=0.041..0.041 rows=0 loops=1)
                           ->  Bitmap Index Scan on element_name  (cost=0.00..4.56 rows=17 width=0) (actual time=0.019..0.019 rows=15 loops=1)
                                 Index Cond: (name = 'head'::text)
                           ->  Bitmap Index Scan on element_parent_id  (cost=0.00..4.62 rows=26 width=0) (actual time=0.016..0.016 rows=5 loops=1)
                                 Index Cond: (parent_id = e1.id)
         ->  Index Scan using element_parent_id on element e3  (cost=0.43..40.38 rows=1 width=8) (actual time=0.147..0.173 rows=1 loops=1)
               Index Cond: (parent_id = e2.id)
               Filter: (lower(name) = 'sysutils'::text)
               Rows Removed by Filter: 88
   ->  Index Scan using element_parent_id on element e4  (cost=0.43..7.07 rows=1 width=8) (actual time=3.097..4.125 rows=1 loops=1)
         Index Cond: (parent_id = e3.id)
         Filter: (lower(name) = 'bacula9-server'::text)
         Rows Removed by Filter: 2380
 Planning Time: 1.608 ms
 Execution Time: 4.898 ms
(29 rows)

The next query involves passing the parent id down to the next level:

freshports.stage=# -- find a port which matches this
freshports.stage=# -- case insensitive
freshports.stage=# 
freshports.stage=# explain analyse
freshports.stage-# SELECT E4.ID, element_pathname(E4.id)
freshports.stage-#   FROM element E4
freshports.stage-#  WHERE lower(E4.name) = lower('py37-mqttwarn') AND E4.parent_id = (
freshports.stage(# SELECT E3.ID
freshports.stage(#   FROM element E3
freshports.stage(#  WHERE lower(E3.name) = lower('sysutils') AND E3.parent_id = (
freshports.stage(# SELECT E2.ID
freshports.stage(#   FROM element E2
freshports.stage(#  WHERE E2.name = 'head' AND E2.parent_id = (
freshports.stage(# SELECT E1.id
freshports.stage(#   FROM element E1 
freshports.stage(#  WHERE E1.parent_id IS NULL AND E1.name = 'ports')));
                                                                        QUERY PLAN                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using element_parent_id on element e4  (cost=68.93..110.05 rows=1 width=36) (actual time=1.902..1.902 rows=0 loops=1)
   Index Cond: (parent_id = $2)
   Filter: (lower(name) = 'py37-mqttwarn'::text)
   Rows Removed by Filter: 2381
   InitPlan 3 (returns $2)
     ->  Index Scan using element_parent_id on element e3  (cost=27.64..68.51 rows=1 width=4) (actual time=0.121..0.134 rows=1 loops=1)
           Index Cond: (parent_id = $1)
           Filter: (lower(name) = 'sysutils'::text)
           Rows Removed by Filter: 88
           InitPlan 2 (returns $1)
             ->  Bitmap Heap Scan on element e2  (cost=23.20..27.21 rows=1 width=4) (actual time=0.061..0.061 rows=1 loops=1)
                   Recheck Cond: ((name = 'head'::text) AND (parent_id = $0))
                   Heap Blocks: exact=1
                   InitPlan 1 (returns $0)
                     ->  Bitmap Heap Scan on element e1  (cost=9.76..13.77 rows=1 width=4) (actual time=0.032..0.032 rows=1 loops=1)
                           Recheck Cond: ((name = 'ports'::text) AND (parent_id IS NULL))
                           Heap Blocks: exact=1
                           ->  BitmapAnd  (cost=9.76..9.76 rows=1 width=0) (actual time=0.027..0.027 rows=0 loops=1)
                                 ->  Bitmap Index Scan on element_name  (cost=0.00..4.56 rows=17 width=0) (actual time=0.011..0.011 rows=38 loops=1)
                                       Index Cond: (name = 'ports'::text)
                                 ->  Bitmap Index Scan on element_parent_id  (cost=0.00..4.95 rows=70 width=0) (actual time=0.013..0.013 rows=100 loops=1)
                                       Index Cond: (parent_id IS NULL)
                   ->  BitmapAnd  (cost=9.43..9.43 rows=1 width=0) (actual time=0.058..0.058 rows=0 loops=1)
                         ->  Bitmap Index Scan on element_name  (cost=0.00..4.56 rows=17 width=0) (actual time=0.016..0.016 rows=15 loops=1)
                               Index Cond: (name = 'head'::text)
                         ->  Bitmap Index Scan on element_parent_id  (cost=0.00..4.62 rows=26 width=0) (actual time=0.040..0.040 rows=5 loops=1)
                               Index Cond: (parent_id = $0)
 Planning Time: 0.235 ms
 Execution Time: 1.945 ms

The chosen query

The chosen query uses a relatively new table: element_pathname

freshports.stage=# \d element_pathname
            Table "public.element_pathname"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 element_id | integer |           | not null | 
 pathname   | text    |           | not null | 
Indexes:
    "element_pathname_pathname" UNIQUE, btree (pathname)
    "element_pathname_element_id" btree (element_id)
Foreign-key constraints:
    "element_pathname_element_id_fkey" FOREIGN KEY (element_id) REFERENCES element(id) ON DELETE CASCADE

freshports.stage=# 

This table is maintained via triggers on the element table.

The same query here is:

freshports.stage=# explain analyse select *
FROM element_pathname where lower(pathname) = '/ports/head/sysutils/py-mqttwarn';
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..19555.74 rows=5361 width=54) (actual time=158.979..160.785 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on element_pathname  (cost=0.00..18019.64 rows=2234 width=54) (actual time=145.503..156.289 rows=0 loops=3)
         Filter: (lower(pathname) = '/ports/head/sysutils/py-mqttwarn'::text)
         Rows Removed by Filter: 351214
 Planning Time: 0.483 ms
 Execution Time: 160.830 ms
(8 rows)

freshports.stage=#

Our query will always be on lower case, so let’s create an index on lower case. I am curious about the size of this index, and it added only 77MB to the database.

freshports.stage=# select pg_size_pretty(pg_indexes_size('element_pathname'));
 pg_size_pretty 
----------------
 107 MB
(1 row)

freshports.stage=# create index element_pathname_pathname_lc on element_pathname ((lower(pathname)));
CREATE INDEX
freshports.stage=# select pg_size_pretty(pg_indexes_size('element_pathname'));
 pg_size_pretty 
----------------
 184 MB
(1 row)

freshports.stage=# select count(*) from element_pathname;
  count  
---------
 1053643
(1 row)

freshports.stage=# 

Now let’s try that query again:

freshports.stage=# explain analyse select *                                                                          
FROM element_pathname where lower(pathname) = '/ports/head/sysutils/py-mqttwarn';
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on element_pathname  (cost=241.38..9537.09 rows=5268 width=54) (actual time=0.133..0.133 rows=1 loops=1)
   Recheck Cond: (lower(pathname) = '/ports/head/sysutils/py-mqttwarn'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on element_pathname_pathname_lc  (cost=0.00..240.06 rows=5268 width=0) (actual time=0.108..0.108 rows=1 loops=1)
         Index Cond: (lower(pathname) = '/ports/head/sysutils/py-mqttwarn'::text)
 Planning Time: 0.087 ms
 Execution Time: 0.155 ms
(7 rows)

freshports.stage=# 

Now we’re using an index scan on that newly created lower case index.

Let’s do a few more queries to see how they go:

freshports.stage=# explain analyse select *
FROM element_pathname where lower(pathname) = '/ports/head/sysutils/bacula-server';
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on element_pathname  (cost=241.38..9537.09 rows=5268 width=54) (actual time=0.050..0.051 rows=1 loops=1)
   Recheck Cond: (lower(pathname) = '/ports/head/sysutils/bacula-server'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on element_pathname_pathname_lc  (cost=0.00..240.06 rows=5268 width=0) (actual time=0.037..0.037 rows=1 loops=1)
         Index Cond: (lower(pathname) = '/ports/head/sysutils/bacula-server'::text)
 Planning Time: 0.073 ms
 Execution Time: 0.067 ms
(7 rows)

freshports.stage=# explain analyse select *
FROM element_pathname where lower(pathname) = '/ports/head/sysutils/bacula9-server';
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on element_pathname  (cost=241.38..9537.09 rows=5268 width=54) (actual time=0.065..0.066 rows=1 loops=1)
   Recheck Cond: (lower(pathname) = '/ports/head/sysutils/bacula9-server'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on element_pathname_pathname_lc  (cost=0.00..240.06 rows=5268 width=0) (actual time=0.049..0.050 rows=1 loops=1)
         Index Cond: (lower(pathname) = '/ports/head/sysutils/bacula9-server'::text)
 Planning Time: 0.086 ms
 Execution Time: 0.088 ms
(7 rows)

freshports.stage=# 

freshports.stage=# explain analyse select *
FROM element_pathname where lower(pathname) = '/ports/head/lang/gcc10-devel';
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on element_pathname  (cost=241.38..9537.09 rows=5268 width=54) (actual time=0.132..0.132 rows=1 loops=1)
   Recheck Cond: (lower(pathname) = '/ports/head/lang/gcc10-devel'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on element_pathname_pathname_lc  (cost=0.00..240.06 rows=5268 width=0) (actual time=0.116..0.117 rows=1 loops=1)
         Index Cond: (lower(pathname) = '/ports/head/lang/gcc10-devel'::text)
 Planning Time: 0.090 ms
 Execution Time: 0.155 ms
(7 rows)

freshports.stage=# 

freshports.stage=# explain analyse select *
FROM element_pathname where lower(pathname) = '/ports/head/lang/perl5.32';
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on element_pathname  (cost=241.38..9537.09 rows=5268 width=54) (actual time=0.059..0.060 rows=1 loops=1)
   Recheck Cond: (lower(pathname) = '/ports/head/lang/perl5.32'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on element_pathname_pathname_lc  (cost=0.00..240.06 rows=5268 width=0) (actual time=0.044..0.044 rows=1 loops=1)
         Index Cond: (lower(pathname) = '/ports/head/lang/perl5.32'::text)
 Planning Time: 0.085 ms
 Execution Time: 0.081 ms
(7 rows)

Yes, I think this will do nicely.

Things change

There is code and tables in here which date back to late 1999 or early 2000. Since then, the database has grown from 15 tables in Nov 2001 to 80 tables today.

I’m not sure when this svg was created or how many tables it represents, but I know its a few years old now.

Database and code evolve over time and it is sometimes interesteing to see the paths they take.