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.
