FreshPorts - packages

Running commands via psql

freshports.dev=# begin;
BEGIN
freshports.dev=#    UPDATE packages_raw PR
freshports.dev-#        SET abi_id  = null,
freshports.dev-#            port_id = null
freshports.dev-#       FROM abi, ports_origin PO
freshports.dev-#      WHERE PR.abi         = 'FreeBSD:13:aarch64'
freshports.dev-#        AND PR.package_set = 'latest'
freshports.dev-#        AND PR.abi         = abi.name
freshports.dev-#        AND PO.port_origin = PR.package_origin;
UPDATE 28626
freshports.dev=# commit;
COMMIT
freshports.dev=# analyse packages_raw;
ANALYZE
freshports.dev=# begin;
BEGIN
freshports.dev=#     UPDATE packages_raw PR
freshports.dev-#        SET abi_id  = abi.id,
freshports.dev-#            port_id = PO.port_id
freshports.dev-#       FROM abi, ports_origin PO
freshports.dev-#      WHERE PR.abi         = 'FreeBSD:13:aarch64'
freshports.dev-#        AND PR.package_set = 'latest'
freshports.dev-#        AND PR.abi         = abi.name
freshports.dev-#        AND PO.port_origin = PR.package_origin;
UPDATE 28626
freshports.dev=#     UPDATE packages p
freshports.dev-#        SET package_version = PR.package_version
freshports.dev-#       FROM packages_raw PR
freshports.dev-#      WHERE P.abi_id           = PR.abi_id
freshports.dev-#        AND P.package_set      = PR.package_set
freshports.dev-#        AND P.package_name     = PR.package_name
freshports.dev-#        AND P.port_id          = PR.port_id
freshports.dev-#        AND P.package_version != PR.package_version
freshports.dev-#        AND PR.abi             = 'FreeBSD:13:aarch64'
freshports.dev-#        AND PR.package_set     = 'latest';
UPDATE 0
freshports.dev=# 
freshports.dev=# 
freshports.dev=# 
freshports.dev=#     INSERT INTO packages (abi_id, port_id, package_version, package_name, package_set)
freshports.dev-#     SELECT abi_id, port_id, package_version, package_name, package_set
freshports.dev-#       FROM packages_raw PR
freshports.dev-#      WHERE NOT EXISTS (
freshports.dev(#      SELECT *
freshports.dev(#        FROM packages P
freshports.dev(#       WHERE P.abi_id           = PR.abi_id
freshports.dev(#         AND P.package_set      = PR.package_set
freshports.dev(#         AND P.package_name     = PR.package_name
freshports.dev(#         AND P.port_id          = PR.port_id
freshports.dev(#         AND P.package_version  = PR.package_version
freshports.dev(#         AND PR.abi             = 'FreeBSD:13:aarch64'
freshports.dev(#         AND PR.package_set     = 'latest')
freshports.dev-#      AND PR.abi_id  IS NOT NULL
freshports.dev-#      AND PR.port_id IS NOT NULL
freshports.dev-#      AND PR.abi             = 'FreeBSD:13:aarch64'
freshports.dev-#      AND PR.package_set     = 'latest';
INSERT 0 0
freshports.dev=# explain analyse
freshports.dev-#     DELETE FROM packages P
freshports.dev-#      WHERE P.abi_id      = 2
freshports.dev-#        AND P.package_set = 'latest'
freshports.dev-#        AND NOT EXISTS (
freshports.dev(#          SELECT *
freshports.dev(#            FROM packages_raw PR
freshports.dev(#           WHERE P.abi_id       = PR.abi_id
freshports.dev(#             AND P.package_set  = PR.package_set
freshports.dev(#             AND P.package_name = PR.package_name
freshports.dev(#             AND P.port_id      = PR.port_id
freshports.dev(#             AND PR.abi         = 'FreeBSD:13:aarch64'
freshports.dev(#             AND PR.package_set = 'latest');
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on packages p  (cost=0.84..4.20 rows=1 width=12) (actual time=164140.089..164140.089 rows=0 loops=1)
   ->  Nested Loop Anti Join  (cost=0.84..4.20 rows=1 width=12) (actual time=164140.087..164140.087 rows=0 loops=1)
         Join Filter: ((p.abi_id = pr.abi_id) AND (p.package_set = pr.package_set) AND (p.package_name = pr.package_name) AND (p.port_id = pr.port_id))
         Rows Removed by Join Filter: 409709625
         ->  Index Scan using fki_packages_abi_id_fk on packages p  (cost=0.42..2.06 rows=1 width=32) (actual time=0.010..13.908 rows=28626 loops=1)
               Index Cond: (abi_id = 2)
               Filter: (package_set = 'latest'::package_sets)
         ->  Index Scan using packages_raw_abi_id_idx on packages_raw pr  (cost=0.42..2.12 rows=1 width=33) (actual time=0.003..4.242 rows=14314 loops=28626)
               Index Cond: (abi_id = 2)
               Filter: ((package_set = 'latest'::package_sets) AND (abi = 'FreeBSD:13:aarch64'::text))
 Planning Time: 0.425 ms
 Execution Time: 164140.125 ms
(12 rows)

freshports.dev=# rollback;
ROLLBACK
freshports.dev=#