Instantly share code, notes, and snippets.

@dlangille /1 - the tables Secret
Last active Aug 22, 2018

Embed
What would you like to do?
Get the matches into the ports_conflicts_matches
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=#
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
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=#
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=#
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 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)
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