Before the last post was published, I forgot to add in the fix for the pathname problem.
freshports.org=# select ElementPathnameUpdateChildren(453745);
It run. A long time. I let it run. For hours. About 16 hours. Then I killed it. I checked the procedure. It made sense. Then I checked the update statement:
freshports.org=# explain analyse UPDATE element_pathname SET pathname = element_pathname(element_id) where element_id = 2335;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Update (cost=0.00..15211.29 rows=1 width=10) (actual time=1877.927..1877.927 rows=0 loops=1)
-> Seq Scan on element_pathname (cost=0.00..15211.29 rows=1 width=10) (actual time=518.410..1366.534 rows=1 loops=1)
Filter: (element_id = 2335)
Total runtime: 1878.080 ms
(4 rows)
freshports.org=#
Whoa! Nearly two seconds for each update. That sequential scan is a killer. Let’s add an index and check the results then.
freshports.org=# \d element_pathname
Table "public.element_pathname"
Column | Type | Modifiers
------------+---------+-----------
element_id | integer | not null
pathname | text | not null
Indexes:
"element_pathname_pathname" UNIQUE, btree (pathname)
Foreign-key constraints:
"element_pathname_element_id_fkey" FOREIGN KEY (element_id) REFERENCES element(id) ON DELETE CASCADE
freshports.org=# create index element_pathname_element_id on element_pathname(element_id);
CREATE INDEX
freshports.org=# explain analyse UPDATE element_pathname SET pathname = element_pathname(element_id) where element_id = 2335;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Update (cost=0.00..8.56 rows=1 width=10) (actual time=0.551..0.551 rows=0 loops=1)
-> Index Scan using element_pathname_element_id on element_pathname (cost=0.00..8.56 rows=1 width=10) (actual time=0.498..0.502 rows=1 loops=1)
Index Cond: (element_id = 2335)
Total runtime: 0.609 ms
(4 rows)
freshports.org=#
Much better. Much much better. Let’s try that query again.
freshports.org=# explain analyse select ElementPathnameUpdateChildren(453745);
QUERY PLAN
----------------------------------------------------------------------------------------------
Result (cost=0.00..0.26 rows=1 width=0) (actual time=149758.049..149758.051 rows=1 loops=1)
Total runtime: 149758.080 ms
(2 rows)
freshports.org=#
Ahah! Only 2.5 minutes. That’s acceptable. When run without the explain analyze, we see the number of updated rows.
freshports.org=# select ElementPathnameUpdateChildren(453745);
elementpathnameupdatechildren
-------------------------------
292265
(1 row)
About 300,000 rows.
Now, on to the src directory.
freshports.org=# begin;
BEGIN
freshports.org=# update element set parent_id = (select pathname_id('/base/head')) where parent_id = (select pathname_id('/src'));
UPDATE 55
freshports.org=# select * from element_pathname where element_id in (select id from element where parent_id = (select pathname_id('/base/head/bin')));
element_id | pathname
------------+-----------------------------
95428 | /base/head/bin/kill
332815 | /base/head/bin/pwait
85980 | /base/head/bin/sh
101053 | /base/head/bin/setfacl
95430 | /base/head/bin/sync
86066 | /base/head/bin/domainname
78380 | /base/head/bin/pwd
86071 | /base/head/bin/hostname
84934 | /base/head/bin/Makefile
90490 | /base/head/bin/Makefile.inc
77539 | /base/head/bin/pax
90811 | /base/head/bin/ln
79318 | /base/head/bin/expr
88146 | /base/head/bin/cat
294410 | /base/head/bin/uuidgen
81031 | /base/head/bin/mv
82955 | /base/head/bin/rmail
101020 | /base/head/bin/chmod
101030 | /base/head/bin/echo
92443 | /base/head/bin/rm
96216 | /base/head/bin/sleep
86068 | /base/head/bin/ed
77537 | /base/head/bin/ls
83336 | /base/head/bin/test
90808 | /base/head/bin/cp
77562 | /base/head/bin/ps
86076 | /base/head/bin/rcp
101051 | /base/head/bin/rmdir
101069 | /base/head/bin/stty
124456 | /base/head/bin/kenv
128894 | /base/head/bin/chflags
308137 | /base/head/bin/pkill
101690 | /base/head/bin/csh
82679 | /base/head/bin/chio
83109 | /base/head/bin/df
83838 | /base/head/bin/dd
85998 | /base/head/bin/date
86073 | /base/head/bin/mkdir
86852 | /base/head/bin/realpath
101038 | /base/head/bin/getfacl
(40 rows)
freshports.org=# commit;
COMMIT
freshports.org=#
And now for docs:
freshports.org=# select Element_Add('/docTMP/head', 'D');
element_add
-------------
453751
(1 row)
freshports.org=# begin;
BEGIN
freshports.org=# update element set parent_id = (select pathname_id('/docTMP/head')) where parent_id = (select pathname_id('/doc'));
UPDATE 27
freshports.org=# select * from element_pathname where element_id in (select id from element where parent_id = (select pathname_id('/docTMP/head/release')));
element_id | pathname
------------+-------------------------------
100765 | /docTMP/head/release/texts
443480 | /docTMP/head/release/9.1.0
88900 | /docTMP/head/release/Makefile
(3 rows)
freshports.org=# commit;
COMMIT
freshports.org=#
Done. All new repo trees created.
