-
-
Save dlangille/65fdf259d7dddf70d040f366c825bbb3 to your computer and use it in GitHub Desktop.
Get the matches into the ports_conflicts_matches
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| freshports.dev=# select * from ports_conflicts; | |
| id | port_id | conflicts_type | item_num | item_value | |
| ----+---------+-------------------+----------+------------------------------ | |
| 21 | 43574 | conflicts_install | 4 | mysqlwsrep.* | |
| 22 | 43574 | conflicts_install | 3 | mysql[0-9].*\-server\-.* | |
| 23 | 43574 | conflicts_install | 1 | mariadb10[013-9]\-server\-.* | |
| 24 | 43574 | conflicts_install | 5 | percona[0-9].*\-server\-.* | |
| 25 | 43574 | conflicts_install | 2 | mariadb5.*\-server\-.* | |
| (5 rows) | |
| freshports.dev=# | |
| freshports.dev=# \d ports_conflicts | |
| Table "public.ports_conflicts" | |
| Column | Type | Collation | Nullable | Default | |
| ----------------+-----------+-----------+----------+--------------------------------------------- | |
| id | bigint | | not null | nextval('ports_conflicts_id_seq'::regclass) | |
| port_id | integer | | not null | | |
| conflicts_type | conflicts | | not null | | |
| item_num | integer | | not null | | |
| item_value | text | | not null | | |
| Indexes: | |
| "ports_conflicts_pkey" PRIMARY KEY, btree (id) | |
| "fki_ports_conflicts_port_id" btree (port_id) | |
| Foreign-key constraints: | |
| "ports_conflicts_port_id" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE | |
| Referenced by: | |
| TABLE "ports_conflicts_matches" CONSTRAINT "ports_conflicts_matches_conflicts_id" FOREIGN KEY (conflicts_id) REFERENCES ports_conflicts(id) ON UPDATE CASCADE ON DELETE CASCADE | |
| freshports.dev=# | |
| freshports.dev=# \d ports_conflicts_matches | |
| Table "public.ports_conflicts_matches" | |
| Column | Type | Collation | Nullable | Default | |
| --------------+---------+-----------+----------+--------- | |
| conflicts_id | bigint | | not null | | |
| port_id | integer | | not null | | |
| Indexes: | |
| "ports_conflicts_matches_pkey" PRIMARY KEY, btree (conflicts_id, port_id) | |
| "fki_ports_conflicts_matches_conflicts_id" btree (conflicts_id) | |
| "fki_ports_conflicts_matches_port_id" btree (port_id) | |
| Foreign-key constraints: | |
| "ports_conflicts_matches_conflicts_id" FOREIGN KEY (conflicts_id) REFERENCES ports_conflicts(id) ON UPDATE CASCADE ON DELETE CASCADE | |
| "ports_conflicts_matches_port_id" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE | |
| freshports.dev=# |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Given the regex in ports_conflicts.item_value find the matching values from the commit_log_ports table | |
| This works rather well: | |
| freshports.dev=# explain analyse | |
| freshports.dev-# SELECT distinct CLP.port_id, CLP.port_name_revision | |
| FROM commit_log_ports CLP | |
| WHERE CLP.port_name_revision ~ '^(mysqlwsrep.*|mysql[0-9].*\-server\-.*|mariadb10[013-9]\-server\-.*|percona[0-9].*\-server\-.*|mariadb5.*\-server\-.*)'; | |
| QUERY PLAN | |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | |
| Unique (cost=19482.04..19482.68 rows=85 width=22) (actual time=497.597..497.856 rows=364 loops=1) | |
| -> Sort (cost=19482.04..19482.25 rows=85 width=22) (actual time=497.596..497.658 rows=852 loops=1) | |
| Sort Key: port_id, port_name_revision | |
| Sort Method: quicksort Memory: 91kB | |
| -> Gather (cost=1000.00..19479.32 rows=85 width=22) (actual time=107.422..497.400 rows=852 loops=1) | |
| Workers Planned: 2 | |
| Workers Launched: 2 | |
| -> Parallel Seq Scan on commit_log_ports clp (cost=0.00..18470.82 rows=35 width=22) (actual time=108.076..493.249 rows=284 loops=3) | |
| Filter: (port_name_revision ~ '^(mysqlwsrep.*|mysql[0-9].*\-server\-.*|mariadb10[013-9]\-server\-.*|percona[0-9].*\-server\-.*|mariadb5.*\-server\-.*)'::text) | |
| Rows Removed by Filter: 307469 | |
| Planning time: 0.417 ms | |
| Execution time: 498.383 ms | |
| (12 rows) | |
| freshports.dev=# | |
| SELECT distinct CLP.port_id, CLP.port_name_revision | |
| FROM commit_log_ports CLP | |
| WHERE CLP.port_name_revision ~ '^(mysqlwsrep.*|mysql[0-9].*\-server\-.*|mariadb10[013-9]\-server\-.*|percona[0-9].*\-server\-.*|mariadb5.*\-server\-.*)'; | |
| port_id | port_name_revision | |
| ---------+------------------------------- | |
| 16965 | mysql51-server-5.1.11 | |
| 16965 | mysql51-server-5.1.12 | |
| 16965 | mysql51-server-5.1.14 | |
| 16965 | mysql51-server-5.1.15 | |
| 16965 | mysql51-server-5.1.16 | |
| 16965 | mysql51-server-5.1.17 | |
| 16965 | mysql51-server-5.1.18 | |
| 16965 | mysql51-server-5.1.19 | |
| 16965 | mysql51-server-5.1.20 | |
| 16965 | mysql51-server-5.1.21 | |
| 16965 | mysql51-server-5.1.22 | |
| 16965 | mysql51-server-5.1.23 | |
| 16965 | mysql51-server-5.1.24 | |
| 16965 | mysql51-server-5.1.25 | |
| 16965 | mysql51-server-5.1.26 | |
| 16965 | mysql51-server-5.1.28 | |
| 16965 | mysql51-server-5.1.29 | |
| 16965 | mysql51-server-5.1.30 | |
| 16965 | mysql51-server-5.1.31 | |
| 16965 | mysql51-server-5.1.32 | |
| 16965 | mysql51-server-5.1.33 | |
| 16965 | mysql51-server-5.1.34 | |
| 16965 | mysql51-server-5.1.35 | |
| 16965 | mysql51-server-5.1.36 | |
| 16965 | mysql51-server-5.1.37 | |
| 16965 | mysql51-server-5.1.38 | |
| 16965 | mysql51-server-5.1.39 | |
| 16965 | mysql51-server-5.1.40 | |
| 16965 | mysql51-server-5.1.41 | |
| 16965 | mysql51-server-5.1.42 | |
| 16965 | mysql51-server-5.1.43 | |
| 16965 | mysql51-server-5.1.44 | |
| 16965 | mysql51-server-5.1.45 | |
| 16965 | mysql51-server-5.1.46 | |
| 16965 | mysql51-server-5.1.47 | |
| 16965 | mysql51-server-5.1.48 | |
| 16965 | mysql51-server-5.1.49 | |
| 16965 | mysql51-server-5.1.5 | |
| 16965 | mysql51-server-5.1.50 | |
| 16965 | mysql51-server-5.1.51 | |
| 16965 | mysql51-server-5.1.52 | |
| 16965 | mysql51-server-5.1.53 | |
| 16965 | mysql51-server-5.1.54 | |
| 16965 | mysql51-server-5.1.55 | |
| 16965 | mysql51-server-5.1.56 | |
| 16965 | mysql51-server-5.1.57 | |
| 16965 | mysql51-server-5.1.58 | |
| 16965 | mysql51-server-5.1.59 | |
| 16965 | mysql51-server-5.1.6 | |
| 16965 | mysql51-server-5.1.60 | |
| 16965 | mysql51-server-5.1.61 | |
| 16965 | mysql51-server-5.1.62 | |
| 16965 | mysql51-server-5.1.63 | |
| 16965 | mysql51-server-5.1.65 | |
| 16965 | mysql51-server-5.1.66 | |
| 16965 | mysql51-server-5.1.67 | |
| 16965 | mysql51-server-5.1.68 | |
| 16965 | mysql51-server-5.1.69 | |
| 16965 | mysql51-server-5.1.7 | |
| 16965 | mysql51-server-5.1.70 | |
| 16965 | mysql51-server-5.1.71 | |
| 16965 | mysql51-server-5.1.72 | |
| 16965 | mysql51-server-5.1.73 | |
| 16965 | mysql51-server-5.1.9 | |
| 26125 | mysql55-server-5.4.3 | |
| 26125 | mysql55-server-5.5.0 | |
| 26125 | mysql55-server-5.5.1 | |
| 26125 | mysql55-server-5.5.10 | |
| 26125 | mysql55-server-5.5.11 | |
| 26125 | mysql55-server-5.5.12 | |
| 26125 | mysql55-server-5.5.13 | |
| 26125 | mysql55-server-5.5.14 | |
| 26125 | mysql55-server-5.5.15 | |
| 26125 | mysql55-server-5.5.16 | |
| 26125 | mysql55-server-5.5.17 | |
| 26125 | mysql55-server-5.5.19 | |
| 26125 | mysql55-server-5.5.2 | |
| 26125 | mysql55-server-5.5.20 | |
| 26125 | mysql55-server-5.5.21 | |
| 26125 | mysql55-server-5.5.22 | |
| 26125 | mysql55-server-5.5.23 | |
| 26125 | mysql55-server-5.5.24 | |
| 26125 | mysql55-server-5.5.25 | |
| 26125 | mysql55-server-5.5.27 | |
| 26125 | mysql55-server-5.5.28 | |
| 26125 | mysql55-server-5.5.29 | |
| 26125 | mysql55-server-5.5.3 | |
| 26125 | mysql55-server-5.5.30 | |
| 26125 | mysql55-server-5.5.31 | |
| 26125 | mysql55-server-5.5.32 | |
| 26125 | mysql55-server-5.5.33 | |
| 26125 | mysql55-server-5.5.34 | |
| 26125 | mysql55-server-5.5.35 | |
| 26125 | mysql55-server-5.5.36 | |
| 26125 | mysql55-server-5.5.37 | |
| 26125 | mysql55-server-5.5.38 | |
| 26125 | mysql55-server-5.5.39 | |
| 26125 | mysql55-server-5.5.4 | |
| 26125 | mysql55-server-5.5.40 | |
| 26125 | mysql55-server-5.5.41 | |
| 26125 | mysql55-server-5.5.42 | |
| 26125 | mysql55-server-5.5.43 | |
| 26125 | mysql55-server-5.5.44 | |
| 26125 | mysql55-server-5.5.46 | |
| 26125 | mysql55-server-5.5.49 | |
| 26125 | mysql55-server-5.5.5 | |
| 26125 | mysql55-server-5.5.50 | |
| 26125 | mysql55-server-5.5.51 | |
| 26125 | mysql55-server-5.5.52 | |
| 26125 | mysql55-server-5.5.53 | |
| 26125 | mysql55-server-5.5.54 | |
| 26125 | mysql55-server-5.5.55 | |
| 26125 | mysql55-server-5.5.56 | |
| 26125 | mysql55-server-5.5.57 | |
| 26125 | mysql55-server-5.5.58 | |
| 26125 | mysql55-server-5.5.59 | |
| 26125 | mysql55-server-5.5.6 | |
| 26125 | mysql55-server-5.5.60 | |
| 26125 | mysql55-server-5.5.61 | |
| 26125 | mysql55-server-5.5.7 | |
| 26125 | mysql55-server-5.5.8 | |
| 26125 | mysql55-server-5.5.9 | |
| 28808 | mariadb53-server-5.2.10 | |
| 28808 | mariadb53-server-5.2.5 | |
| 28808 | mariadb53-server-5.2.6 | |
| 28808 | mariadb53-server-5.2.7 | |
| 28808 | mariadb53-server-5.2.8 | |
| 28808 | mariadb53-server-5.2.9 | |
| 28808 | mariadb53-server-5.3.11 | |
| 28808 | mariadb53-server-5.3.12 | |
| 28808 | mariadb53-server-5.3.6 | |
| 28808 | mariadb53-server-5.3.7 | |
| 28808 | mariadb53-server-5.3.8 | |
| 31147 | percona55-server-5.5.19.24.0 | |
| 31147 | percona55-server-5.5.28.29.2 | |
| 31147 | percona55-server-5.5.28.29.3 | |
| 31147 | percona55-server-5.5.29.29.4 | |
| 31147 | percona55-server-5.5.29.30.0 | |
| 31147 | percona55-server-5.5.30.30.1 | |
| 31147 | percona55-server-5.5.30.30.2 | |
| 31147 | percona55-server-5.5.31.30.3 | |
| 31147 | percona55-server-5.5.32.31.0 | |
| 31147 | percona55-server-5.5.33.31.1 | |
| 31147 | percona55-server-5.5.34.32.0 | |
| 31147 | percona55-server-5.5.35.33.0 | |
| 31147 | percona55-server-5.5.36.34.1 | |
| 31147 | percona55-server-5.5.38.35.2 | |
| 31147 | percona55-server-5.5.40.36.1 | |
| 31147 | percona55-server-5.5.41.37.0 | |
| 31147 | percona55-server-5.5.43.37.2 | |
| 31147 | percona55-server-5.5.44.37.3 | |
| 31147 | percona55-server-5.5.46.37.5 | |
| 31147 | percona55-server-5.5.48.37.8 | |
| 31147 | percona55-server-5.5.51.38.1 | |
| 31147 | percona55-server-5.5.54.38.6 | |
| 31147 | percona55-server-5.5.58.38.10 | |
| 31147 | percona55-server-5.5.59.38.11 | |
| 31147 | percona55-server-5.5.60.38.12 | |
| 32193 | mariadb55-server-5.5.28a | |
| 32193 | mariadb55-server-5.5.29 | |
| 32193 | mariadb55-server-5.5.30 | |
| 32193 | mariadb55-server-5.5.31 | |
| 32193 | mariadb55-server-5.5.33a | |
| 32193 | mariadb55-server-5.5.35 | |
| 32193 | mariadb55-server-5.5.37 | |
| 32193 | mariadb55-server-5.5.38 | |
| 32193 | mariadb55-server-5.5.39 | |
| 32193 | mariadb55-server-5.5.40 | |
| 32193 | mariadb55-server-5.5.41 | |
| 32193 | mariadb55-server-5.5.43 | |
| 32193 | mariadb55-server-5.5.44 | |
| 32193 | mariadb55-server-5.5.46 | |
| 32193 | mariadb55-server-5.5.47 | |
| 32193 | mariadb55-server-5.5.49 | |
| 32193 | mariadb55-server-5.5.50 | |
| 32193 | mariadb55-server-5.5.51 | |
| 32193 | mariadb55-server-5.5.52 | |
| 32193 | mariadb55-server-5.5.53 | |
| 32193 | mariadb55-server-5.5.54 | |
| 32193 | mariadb55-server-5.5.55 | |
| 32193 | mariadb55-server-5.5.56 | |
| 32193 | mariadb55-server-5.5.57 | |
| 32193 | mariadb55-server-5.5.58 | |
| 32193 | mariadb55-server-5.5.59 | |
| 32193 | mariadb55-server-5.5.60 | |
| 32193 | mariadb55-server-5.5.61 | |
| 32408 | mysql56-server-5.6.10 | |
| 32408 | mysql56-server-5.6.11 | |
| 32408 | mysql56-server-5.6.12 | |
| 32408 | mysql56-server-5.6.13 | |
| 32408 | mysql56-server-5.6.14 | |
| 32408 | mysql56-server-5.6.15 | |
| 32408 | mysql56-server-5.6.16 | |
| 32408 | mysql56-server-5.6.17 | |
| 32408 | mysql56-server-5.6.19 | |
| 32408 | mysql56-server-5.6.20 | |
| 32408 | mysql56-server-5.6.21 | |
| 32408 | mysql56-server-5.6.22 | |
| 32408 | mysql56-server-5.6.23 | |
| 32408 | mysql56-server-5.6.24 | |
| 32408 | mysql56-server-5.6.25 | |
| 32408 | mysql56-server-5.6.26 | |
| 32408 | mysql56-server-5.6.27 | |
| 32408 | mysql56-server-5.6.30 | |
| 32408 | mysql56-server-5.6.32 | |
| 32408 | mysql56-server-5.6.33 | |
| 32408 | mysql56-server-5.6.34 | |
| 32408 | mysql56-server-5.6.35 | |
| 32408 | mysql56-server-5.6.36 | |
| 32408 | mysql56-server-5.6.37 | |
| 32408 | mysql56-server-5.6.38 | |
| 32408 | mysql56-server-5.6.39 | |
| 32408 | mysql56-server-5.6.40 | |
| 32408 | mysql56-server-5.6.41 | |
| 32558 | percona56-server-5.6.10.a60.2 | |
| 32558 | percona56-server-5.6.11.r60.3 | |
| 32558 | percona56-server-5.6.12.r60.4 | |
| 32558 | percona56-server-5.6.13.r60.5 | |
| 32558 | percona56-server-5.6.13.r60.6 | |
| 32558 | percona56-server-5.6.13.r61.0 | |
| 32558 | percona56-server-5.6.14.r62.0 | |
| 32558 | percona56-server-5.6.15.r63.0 | |
| 32558 | percona56-server-5.6.16.64.1 | |
| 32558 | percona56-server-5.6.17.66.0 | |
| 32558 | percona56-server-5.6.19.67.0 | |
| 32558 | percona56-server-5.6.21.70.1 | |
| 32558 | percona56-server-5.6.22.71.0 | |
| 32558 | percona56-server-5.6.22.72.0 | |
| 32558 | percona56-server-5.6.24.72.2 | |
| 32558 | percona56-server-5.6.26.74.0 | |
| 32558 | percona56-server-5.6.27.75.0 | |
| 32558 | percona56-server-5.6.29.76.2 | |
| 32558 | percona56-server-5.6.31.77.0 | |
| 32558 | percona56-server-5.6.32.78.0 | |
| 32558 | percona56-server-5.6.33.79.0 | |
| 32558 | percona56-server-5.6.35.80.0 | |
| 32558 | percona56-server-5.6.36.82.0 | |
| 32558 | percona56-server-5.6.38.83.0 | |
| 32558 | percona56-server-5.6.39.83.1 | |
| 35631 | mariadb100-server-10.0.14 | |
| 35631 | mariadb100-server-10.0.15 | |
| 35631 | mariadb100-server-10.0.16 | |
| 35631 | mariadb100-server-10.0.17 | |
| 35631 | mariadb100-server-10.0.21 | |
| 35631 | mariadb100-server-10.0.22 | |
| 35631 | mariadb100-server-10.0.23 | |
| 35631 | mariadb100-server-10.0.25 | |
| 35631 | mariadb100-server-10.0.26 | |
| 35631 | mariadb100-server-10.0.27 | |
| 35631 | mariadb100-server-10.0.29 | |
| 35631 | mariadb100-server-10.0.30 | |
| 35631 | mariadb100-server-10.0.31 | |
| 35631 | mariadb100-server-10.0.32 | |
| 35631 | mariadb100-server-10.0.33 | |
| 35631 | mariadb100-server-10.0.34 | |
| 35631 | mariadb100-server-10.0.35 | |
| 35631 | mariadb100-server-10.0.36 | |
| 37760 | mysql57-server-5.6.27 | |
| 37760 | mysql57-server-5.7.10 | |
| 37760 | mysql57-server-5.7.12 | |
| 37760 | mysql57-server-5.7.13 | |
| 37760 | mysql57-server-5.7.15 | |
| 37760 | mysql57-server-5.7.17 | |
| 37760 | mysql57-server-5.7.18 | |
| 37760 | mysql57-server-5.7.19 | |
| 37760 | mysql57-server-5.7.20 | |
| 37760 | mysql57-server-5.7.21 | |
| 37760 | mysql57-server-5.7.22 | |
| 37760 | mysql57-server-5.7.23 | |
| 38042 | mariadb101-server-10.1.11 | |
| 38042 | mariadb101-server-10.1.13 | |
| 38042 | mariadb101-server-10.1.14 | |
| 38042 | mariadb101-server-10.1.16 | |
| 38042 | mariadb101-server-10.1.17 | |
| 38042 | mariadb101-server-10.1.18 | |
| 38042 | mariadb101-server-10.1.19 | |
| 38042 | mariadb101-server-10.1.20 | |
| 38042 | mariadb101-server-10.1.21 | |
| 38042 | mariadb101-server-10.1.22 | |
| 38042 | mariadb101-server-10.1.23 | |
| 38042 | mariadb101-server-10.1.24 | |
| 38042 | mariadb101-server-10.1.25 | |
| 38042 | mariadb101-server-10.1.26 | |
| 38042 | mariadb101-server-10.1.28 | |
| 38042 | mariadb101-server-10.1.29 | |
| 38042 | mariadb101-server-10.1.30 | |
| 38042 | mariadb101-server-10.1.31 | |
| 38042 | mariadb101-server-10.1.32 | |
| 38042 | mariadb101-server-10.1.33 | |
| 38042 | mariadb101-server-10.1.35 | |
| 39708 | mariadb101-server-10.1.19 | |
| 39843 | mysql56-server-5.6.34 | |
| 40409 | mariadb55-server-5.5.54 | |
| 40421 | mariadb101-server-10.1.20 | |
| 40421 | mariadb101-server-10.1.21 | |
| 40421 | mariadb101-server-10.1.22 | |
| 40520 | mariadb100-server-10.0.27 | |
| 40520 | mariadb100-server-10.0.29 | |
| 40520 | mariadb100-server-10.0.30 | |
| 40654 | mysql80-server-8.0.0 | |
| 40654 | mysql80-server-8.0.11 | |
| 40654 | mysql80-server-8.0.12 | |
| 40654 | mysql80-server-8.0.2 | |
| 40750 | percona57-server-5.7.16.10 | |
| 40750 | percona57-server-5.7.20.18 | |
| 40750 | percona57-server-5.7.21.20 | |
| 41123 | mysql56-server-5.6.35 | |
| 41266 | mysql57-server-5.7.17 | |
| 41490 | mysqlwsrep56-server-5.6.35 | |
| 41490 | mysqlwsrep56-server-5.6.36 | |
| 41490 | mysqlwsrep56-server-5.6.38 | |
| 41490 | mysqlwsrep56-server-5.6.39 | |
| 42249 | mysql56-server-5.6.35 | |
| 42249 | mysql56-server-5.6.36 | |
| 42255 | mariadb100-server-10.0.30 | |
| 42255 | mariadb100-server-10.0.31 | |
| 42259 | percona57-server-5.7.16.10 | |
| 42376 | mariadb101-server-10.1.22 | |
| 42376 | mariadb101-server-10.1.23 | |
| 42383 | mariadb55-server-5.5.54 | |
| 42383 | mariadb55-server-5.5.55 | |
| 42846 | mysql80-server-8.0.0 | |
| 42892 | mysql57-server-5.7.17 | |
| 42892 | mysql57-server-5.7.18 | |
| 44391 | mysqlwsrep57-server-5.7.18 | |
| 44391 | mysqlwsrep57-server-5.7.20 | |
| 44391 | mysqlwsrep57-server-5.7.21 | |
| 44539 | percona56-server-5.6.35.80.0 | |
| 44540 | percona55-server-5.5.54.38.6 | |
| 44547 | mysql57-server-5.7.18 | |
| 44547 | mysql57-server-5.7.19 | |
| 44644 | mariadb101-server-10.1.24 | |
| 44644 | mariadb101-server-10.1.26 | |
| 44827 | mariadb100-server-10.0.32 | |
| 45081 | mysql56-server-5.6.37 | |
| 45399 | mysql57-server-5.7.20 | |
| 45440 | mariadb55-server-5.5.58 | |
| 45526 | mariadb100-server-10.0.33 | |
| 45987 | mysql56-server-5.6.38 | |
| 46004 | mysql55-server-5.5.58 | |
| 46302 | mysql56-server-5.6.38 | |
| 46302 | mysql56-server-5.6.39 | |
| 46502 | percona57-server-5.7.20.18 | |
| 46670 | mysql57-server-5.7.21 | |
| 46699 | mariadb55-server-5.5.59 | |
| 46760 | mariadb101-server-10.1.30 | |
| 46760 | mariadb101-server-10.1.31 | |
| 46837 | mysql55-server-5.5.59 | |
| 46955 | mariadb100-server-10.0.34 | |
| 47245 | percona55-server-5.5.59.38.11 | |
| 48244 | mariadb55-server-5.5.60 | |
| 48249 | mysql80-server-8.0.2 | |
| 48566 | mysql56-server-5.6.40 | |
| 48568 | mysql57-server-5.7.22 | |
| 48611 | mysql55-server-5.5.60 | |
| 48630 | mariadb101-server-10.1.33 | |
| 48881 | mariadb103-server-10.3.7 | |
| 48881 | mariadb103-server-10.3.8 | |
| 49666 | mariadb100-server-10.0.36 | |
| 49668 | mariadb55-server-5.5.61 | |
| 49671 | mariadb101-server-10.1.35 | |
| 49699 | mysql56-server-5.6.41 | |
| 49702 | mysql57-server-5.7.23 | |
| 49704 | mysql80-server-8.0.12 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| In the above output, we have port_id, which is the main goal of the query. To populate the ports_conflicts_matches table, | |
| we also need the matching ports_conflicts.id value (which uniquely identifies the regex used to get the values). | |
| Something like this works: | |
| SELECT distinct PC.id, CLP.port_id, CLP.port_name_revision | |
| FROM commit_log_ports CLP JOIN ports_conflicts PC | |
| ON (PC.port_id = 43574 AND CLP.port_name_revision ~ ('^' || PC.item_value || '$')); | |
| However, it takes 5000ms: | |
| freshports.dev=# explain analyse | |
| freshports.dev-# SELECT distinct PC.id, CLP.port_id, CLP.port_name_revision | |
| freshports.dev-# FROM commit_log_ports CLP JOIN ports_conflicts PC | |
| freshports.dev-# ON (PC.port_id = 43574 AND CLP.port_name_revision ~ ('^' || PC.item_value || '$')); | |
| QUERY PLAN | |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Unique (cost=117442.27..117673.05 rows=23078 width=30) (actual time=5240.532..5240.813 rows=364 loops=1) | |
| -> Sort (cost=117442.27..117499.97 rows=23078 width=30) (actual time=5240.531..5240.592 rows=852 loops=1) | |
| Sort Key: pc.id, clp.port_id, clp.port_name_revision | |
| Sort Method: quicksort Memory: 91kB | |
| -> Nested Loop (cost=4.19..115216.78 rows=23078 width=30) (actual time=1052.774..5239.609 rows=852 loops=1) | |
| Join Filter: (clp.port_name_revision ~ (('^'::text || pc.item_value) || '$'::text)) | |
| Rows Removed by Join Filter: 4615438 | |
| -> Seq Scan on commit_log_ports clp (cost=0.00..22894.01 rows=923101 width=22) (actual time=0.104..192.163 rows=923258 loops=1) | |
| -> Materialize (cost=4.19..12.69 rows=5 width=40) (actual time=0.000..0.000 rows=5 loops=923258) | |
| -> Bitmap Heap Scan on ports_conflicts pc (cost=4.19..12.66 rows=5 width=40) (actual time=0.018..0.019 rows=5 loops=1) | |
| Recheck Cond: (port_id = 43574) | |
| Heap Blocks: exact=1 | |
| -> Bitmap Index Scan on fki_ports_conflicts_port_id (cost=0.00..4.19 rows=5 width=0) (actual time=0.010..0.010 rows=25 loops=1) | |
| Index Cond: (port_id = 43574) | |
| Planning time: 0.190 ms | |
| Execution time: 5240.881 ms | |
| (16 rows) | |
| freshports.dev=# |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| freshports.dev=# SELECT id, '^' || item_value || '$' AS regex_pattern FROM ports_conflicts WHERE port_id = 43574; | |
| id | regex_pattern | |
| ----+-------------------------------- | |
| 21 | ^mysqlwsrep.*$ | |
| 22 | ^mysql[0-9].*\-server\-.*$ | |
| 23 | ^mariadb10[013-9]\-server\-.*$ | |
| 24 | ^percona[0-9].*\-server\-.*$ | |
| 25 | ^mariadb5.*\-server\-.*$ | |
| (5 rows) | |
| Let's move on from that: | |
| freshports.dev=# explain analyse SELECT distinct TMP.id, CLP.port_id | |
| FROM commit_log_ports CLP JOIN (SELECT id, '^' || item_value || '$' AS regex_pattern FROM ports_conflicts WHERE port_id = 43574) AS TMP | |
| ON (CLP.port_name_revision ~ TMP.regex_pattern); | |
| QUERY PLAN | |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Unique (cost=116889.27..117062.36 rows=23078 width=12) (actual time=5299.707..5299.872 rows=60 loops=1) | |
| -> Sort (cost=116889.27..116946.97 rows=23078 width=12) (actual time=5299.706..5299.763 rows=852 loops=1) | |
| Sort Key: ports_conflicts.id, clp.port_id | |
| Sort Method: quicksort Memory: 64kB | |
| -> Nested Loop (cost=4.19..115216.78 rows=23078 width=12) (actual time=1061.121..5299.199 rows=852 loops=1) | |
| Join Filter: (clp.port_name_revision ~ (('^'::text || ports_conflicts.item_value) || '$'::text)) | |
| Rows Removed by Join Filter: 4615438 | |
| -> Seq Scan on commit_log_ports clp (cost=0.00..22894.01 rows=923101 width=22) (actual time=0.113..187.865 rows=923258 loops=1) | |
| -> Materialize (cost=4.19..12.69 rows=5 width=40) (actual time=0.000..0.000 rows=5 loops=923258) | |
| -> Bitmap Heap Scan on ports_conflicts (cost=4.19..12.66 rows=5 width=40) (actual time=0.018..0.019 rows=5 loops=1) | |
| Recheck Cond: (port_id = 43574) | |
| Heap Blocks: exact=1 | |
| -> Bitmap Index Scan on fki_ports_conflicts_port_id (cost=0.00..4.19 rows=5 width=0) (actual time=0.010..0.010 rows=25 loops=1) | |
| Index Cond: (port_id = 43574) | |
| Planning time: 0.195 ms | |
| Execution time: 5299.926 ms | |
| (16 rows) | |
| freshports.dev=# | |
| SELECT distinct TMP.id, CLP.port_id | |
| FROM commit_log_ports CLP JOIN (SELECT id, '^' || item_value || '$' AS regex_pattern FROM ports_conflicts WHERE port_id = 43574) AS TMP | |
| ON (CLP.port_name_revision ~ TMP.regex_pattern); | |
| id | port_id | |
| ----+--------- | |
| 21 | 41490 | |
| 21 | 44391 | |
| 22 | 16965 | |
| 22 | 26125 | |
| 22 | 32408 | |
| 22 | 37760 | |
| 22 | 39843 | |
| 22 | 40654 | |
| 22 | 41123 | |
| 22 | 41266 | |
| 22 | 42249 | |
| 22 | 42846 | |
| 22 | 42892 | |
| 22 | 44547 | |
| 22 | 45081 | |
| 22 | 45399 | |
| 22 | 45987 | |
| 22 | 46004 | |
| 22 | 46302 | |
| 22 | 46670 | |
| 22 | 46837 | |
| 22 | 48249 | |
| 22 | 48566 | |
| 22 | 48568 | |
| 22 | 48611 | |
| 22 | 49699 | |
| 22 | 49702 | |
| 22 | 49704 | |
| 23 | 35631 | |
| 23 | 38042 | |
| 23 | 39708 | |
| 23 | 40421 | |
| 23 | 40520 | |
| 23 | 42255 | |
| 23 | 42376 | |
| 23 | 44644 | |
| 23 | 44827 | |
| 23 | 45526 | |
| 23 | 46760 | |
| 23 | 46955 | |
| 23 | 48630 | |
| 23 | 48881 | |
| 23 | 49666 | |
| 23 | 49671 | |
| 24 | 31147 | |
| 24 | 32558 | |
| 24 | 40750 | |
| 24 | 42259 | |
| 24 | 44539 | |
| 24 | 44540 | |
| 24 | 46502 | |
| 24 | 47245 | |
| 25 | 28808 | |
| 25 | 32193 | |
| 25 | 40409 | |
| 25 | 42383 | |
| 25 | 45440 | |
| 25 | 46699 | |
| 25 | 48244 | |
| 25 | 49668 | |
| (60 rows) | |
| freshports.dev=# |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Ideally, I'd like to get the output from #4 above with the speed of this query: | |
| freshports.dev=# explain analyse SELECT distinct TMP.id | |
| FROM commit_log_ports CLP JOIN (SELECT id, '^' || item_value || '$' AS regex_pattern FROM ports_conflicts WHERE port_id = 43574 AND id = 22) AS TMP | |
| ON (CLP.port_name_revision ~ '^mysql[0-9].*\-server\-.*$'); | |
| QUERY PLAN | |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Unique (cost=0.57..20.91 rows=1 width=8) (actual time=2.051..4.863 rows=1 loops=1) | |
| -> Nested Loop (cost=0.57..20.91 rows=85 width=8) (actual time=2.050..4.839 rows=408 loops=1) | |
| -> Index Scan using ports_conflicts_pkey on ports_conflicts (cost=0.15..8.17 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=1) | |
| Index Cond: (id = 22) | |
| Filter: (port_id = 43574) | |
| -> Index Only Scan using commit_log_ports_port_name_revision on commit_log_ports clp (cost=0.42..11.89 rows=85 width=0) (actual time=2.029..4.767 rows=408 loops=1) | |
| Index Cond: ((port_name_revision >= 'mysql'::text) AND (port_name_revision < 'mysqm'::text)) | |
| Filter: (port_name_revision ~ '^mysql[0-9].*\-server\-.*$'::text) | |
| Rows Removed by Filter: 1612 | |
| Heap Fetches: 0 | |
| Planning time: 0.399 ms | |
| Execution time: 4.896 ms | |
| (12 rows) | |
| freshports.dev=# | |
| -> Index Scan using commit_log_ports_port_name_revision on commit_log_ports clp (cost=0.42..8.45 rows=85 width=4) (actual time=0.205..1.072 rows=135 loops=1) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| This runs rather well | |
| CREATE OR REPLACE FUNCTION port_conflicts4(bigint, text) RETURNS SETOF ports_conflicts_matches AS $$ | |
| SELECT DISTINCT PC.id, CLP.port_id | |
| FROM commit_log_ports CLP JOIN ports_conflicts PC | |
| ON (PC.id = $1 AND CLP.port_name_revision ~ $2); | |
| $$ LANGUAGE SQL STABLE; | |
| freshports.dev=# SELECT * FROM port_conflicts4(22, '^mysql[0-9].*\-server\-.*$'); | |
| conflicts_id | port_id | |
| --------------+--------- | |
| 22 | 16965 | |
| 22 | 26125 | |
| 22 | 32408 | |
| 22 | 37760 | |
| 22 | 39843 | |
| 22 | 40654 | |
| 22 | 41123 | |
| 22 | 41266 | |
| 22 | 42249 | |
| 22 | 42846 | |
| 22 | 42892 | |
| 22 | 44547 | |
| 22 | 45081 | |
| 22 | 45399 | |
| 22 | 45987 | |
| 22 | 46004 | |
| 22 | 46302 | |
| 22 | 46670 | |
| 22 | 46837 | |
| 22 | 48249 | |
| 22 | 48566 | |
| 22 | 48568 | |
| 22 | 48611 | |
| 22 | 49699 | |
| 22 | 49702 | |
| 22 | 49704 | |
| (26 rows) | |
| freshports.dev=# explain analyse SELECT * FROM port_conflicts4(22, '^mysql[0-9].*\-server\-.*$'); | |
| QUERY PLAN | |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Unique (cost=586.60..587.03 rows=85 width=12) (actual time=6.846..6.917 rows=26 loops=1) | |
| -> Sort (cost=586.60..586.82 rows=85 width=12) (actual time=6.846..6.873 rows=408 loops=1) | |
| Sort Key: clp.port_id | |
| Sort Method: quicksort Memory: 44kB | |
| -> Nested Loop (cost=6.14..583.88 rows=85 width=12) (actual time=1.685..6.711 rows=408 loops=1) | |
| -> Index Only Scan using ports_conflicts_pkey on ports_conflicts pc (cost=0.15..8.17 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1) | |
| Index Cond: (id = '22'::bigint) | |
| Heap Fetches: 1 | |
| -> Bitmap Heap Scan on commit_log_ports clp (cost=5.99..574.86 rows=85 width=4) (actual time=1.666..6.624 rows=408 loops=1) | |
| Filter: (port_name_revision ~ '^mysql[0-9].*\-server\-.*$'::text) | |
| Rows Removed by Filter: 1612 | |
| Heap Blocks: exact=1218 | |
| -> Bitmap Index Scan on commit_log_ports_port_name_revision (cost=0.00..5.96 rows=154 width=0) (actual time=0.488..0.488 rows=2020 loops=1) | |
| Index Cond: ((port_name_revision >= 'mysql'::text) AND (port_name_revision < 'mysqm'::text)) | |
| Planning time: 0.468 ms | |
| Execution time: 6.954 ms | |
| (16 rows) | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT PC.* | |
| FROM (SELECT id, '^' || item_value || '$' AS regex_pattern FROM ports_conflicts WHERE port_id = 43574) AS tmp | |
| JOIN port_conflicts4(TMP.id, TMP.regex_pattern) AS PC; | |
| SELECT port_conflicts4(id, '^' || item_value || '$') FROM ports_conflicts WHERE port_id = 43574; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment