-
-
Save dlangille/ecaf398204fa50540ac54869462293ca to your computer and use it in GitHub Desktop.
A faster solution - use plpgsql
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
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 STRICT; |
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
CREATE OR REPLACE FUNCTION port_conflicts5(bigint, text) RETURNS SETOF ports_conflicts_matches AS $$ | |
DECLARE | |
a_ports_conflicts_id ALIAS FOR $1; | |
a_regex ALIAS FOR $2; | |
l_regex text; | |
r ports_conflicts_matches%rowtype; | |
BEGIN | |
l_regex := '^(' || a_regex || ')'; | |
FOR r in SELECT distinct a_ports_conflicts_id, port_id FROM commit_log_ports WHERE port_name_revision ~ l_regex | |
LOOP | |
RETURN NEXT r; | |
END LOOP; | |
END; | |
$$ LANGUAGE 'plpgsql' STABLE; |
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=# explain analyse SELECT PC4.* | |
FROM ports_conflicts PC, LATERAL (SELECT * FROM port_conflicts5(PC.id, '^' || PC.item_value || '$')) PC4; | |
QUERY PLAN | |
-------------------------------------------------------------------------------------------------------------------------- | |
Nested Loop (cost=0.26..20420.46 rows=1020000 width=12) (actual time=0.507..11.971 rows=60 loops=1) | |
-> Seq Scan on ports_conflicts pc (cost=0.00..20.20 rows=1020 width=40) (actual time=0.008..0.010 rows=5 loops=1) | |
-> Function Scan on port_conflicts5 (cost=0.26..10.26 rows=1000 width=12) (actual time=2.388..2.389 rows=12 loops=5) | |
Planning time: 0.066 ms | |
Execution time: 11.998 ms | |
(5 rows) | |
freshports.dev=# SELECT PC4.* | |
FROM ports_conflicts PC, LATERAL (SELECT * FROM port_conflicts5(PC.id, '^' || PC.item_value || '$')) PC4; | |
conflicts_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 | 38042 | |
23 | 44644 | |
23 | 40421 | |
23 | 48630 | |
23 | 39708 | |
23 | 40520 | |
23 | 48881 | |
23 | 46760 | |
23 | 49666 | |
23 | 35631 | |
23 | 44827 | |
23 | 42376 | |
23 | 45526 | |
23 | 49671 | |
23 | 42255 | |
23 | 46955 | |
24 | 46502 | |
24 | 44539 | |
24 | 47245 | |
24 | 44540 | |
24 | 31147 | |
24 | 42259 | |
24 | 40750 | |
24 | 32558 | |
25 | 46699 | |
25 | 32193 | |
25 | 40409 | |
25 | 28808 | |
25 | 49668 | |
25 | 45440 | |
25 | 48244 | |
25 | 42383 | |
(60 rows) | |
freshports.dev=# |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment