Skip to content

Instantly share code, notes, and snippets.

@dlangille
Last active August 22, 2018 15:12
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dlangille/ecaf398204fa50540ac54869462293ca to your computer and use it in GitHub Desktop.
Save dlangille/ecaf398204fa50540ac54869462293ca to your computer and use it in GitHub Desktop.
A faster solution - use plpgsql
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;
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;
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