Sep 072012
 

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.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive