Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t (txt text);

INSERT INTO t
SELECT '/articles/' || g || '/'
FROM generate_series(1,10000) g         -- '/articles/123/

UNION ALL 
SELECT '/articles/new/' || g || '/'
FROM generate_series(1,2000) g          -- '/articles/new/123/

UNION ALL 
SELECT '/articles/new-' || g || '/'
FROM generate_series(1,300) g           -- '/articles/new-123/

UNION ALL 
SELECT '/articles/new/'                 -- '/articles/new/
FROM generate_series(1,40) g

UNION ALL 
SELECT '/articles/new-/foo'             -- '/articles/new-/foo
FROM generate_series(1,5) g
;
12345 rows affected

VACUUM ANALYZE t;
-- results
SELECT count(txt LIKE '/articles/%' AND txt <> '/articles/new/' OR NULL) AS test1
     , count(txt LIKE '/articles/%' AND txt NOT LIKE '/articles/new/%' OR NULL) AS test2
     , count(txt ~    '^/articles(?!/new/)/([\w-]+)/$' OR NULL) AS test3
     , count(txt ~    '^/articles/(?!new/)[\w-]+/$' OR NULL) AS test4
FROM   t;

-- the same with aggregate FILTER clause (Postgres 9.4+)
SELECT count(*) FILTER (WHERE txt LIKE '/articles/%' AND txt <> '/articles/new/') AS test1
     , count(*) FILTER (WHERE txt LIKE '/articles/%' AND txt NOT LIKE '/articles/new/%') AS test2
     , count(*) FILTER (WHERE txt ~  '^/articles(?!/new/)/([\w-]+)/$') AS test3
     , count(*) FILTER (WHERE txt ~  '^/articles/(?!new/)[\w-]+/$') AS test4
FROM   t;


test1
test2
test3
test4


12305
10305
10300
10300




test1
test2
test3
test4


12305
10305
10300
10300


-- performance

EXPLAIN (ANALYZE, TIMING OFF)
SELECT count(*)
FROM   t
WHERE  txt LIKE  '/articles/%'
AND    txt <>    '/articles/new/';


QUERY PLAN


Aggregate  (cost=284.94..284.94 rows=1 width=8) (actual rows=1 loops=1)

  ->  Seq Scan on t  (cost=0.00..254.17 rows=12304 width=0) (actual rows=12305 loops=1)

        Filter: ((txt ~~ '/articles/%'::text) AND (txt <> '/articles/new/'::text))

        Rows Removed by Filter: 40

Planning Time: 0.075 ms

Execution Time: 2.014 ms


EXPLAIN (ANALYZE, TIMING OFF)
SELECT count(*)
FROM   t
WHERE  txt LIKE     '/articles/%'
AND    txt NOT LIKE '/articles/new/%';


QUERY PLAN


Aggregate  (cost=279.96..279.97 rows=1 width=8) (actual rows=1 loops=1)

  ->  Seq Scan on t  (cost=0.00..254.17 rows=10316 width=0) (actual rows=10305 loops=1)

        Filter: ((txt ~~ '/articles/%'::text) AND (txt !~~ '/articles/new/%'::text))

        Rows Removed by Filter: 2040

Planning Time: 0.050 ms

Execution Time: 2.260 ms


EXPLAIN (ANALYZE, TIMING OFF)
SELECT count(*)
FROM   t
WHERE  txt ~ '^/articles(?!/new/)/([\w-]+)/$';


QUERY PLAN


Aggregate  (cost=249.09..249.10 rows=1 width=8) (actual rows=1 loops=1)

  ->  Seq Scan on t  (cost=0.00..223.31 rows=10312 width=0) (actual rows=10300 loops=1)

        Filter: (txt ~ '^/articles(?!/new/)/([\w-]+)/$'::text)

        Rows Removed by Filter: 2045

Planning Time: 0.384 ms

Execution Time: 38.641 ms


EXPLAIN (ANALYZE, TIMING OFF)
SELECT count(*)
FROM   t
WHERE  txt ~ '^/articles/(?!new/)[\w-]+/$';


QUERY PLAN


Aggregate  (cost=249.09..249.10 rows=1 width=8) (actual rows=1 loops=1)

  ->  Seq Scan on t  (cost=0.00..223.31 rows=10312 width=0) (actual rows=10300 loops=1)

        Filter: (txt ~ '^/articles/(?!new/)[\w-]+/$'::text)

        Rows Removed by Filter: 2045

Planning Time: 0.351 ms

Execution Time: 37.676 ms