clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36306 in the last week).

-- Test with "real" numbers, scaled down by factor 10 to keep it short CREATE TABLE prefix (code text UNIQUE NOT NULL, name text, price int); INSERT INTO prefix SELECT DISTINCT ON (1) (random() * 100000)::int::text, g::text, g%500 FROM generate_series (1,2100) g LIMIT 2000; -- 2.000 random prefixes CREATE TABLE num (number text NOT NULL, time int); INSERT INTO num SELECT '8' || (random() * 100000000)::int::text, g FROM generate_series (1,17000) g; -- 17.000 random numbers -- Fastest CREATE INDEX num_trgm_gin_idx ON num USING gin (right(number, -1) gin_trgm_ops); -- Not quite as fast -- CREATE INDEX num_trgm_gist_idx ON num USING gist (right(number, -1) gist_trgm_ops); ANALYZE prefix; ANALYZE num;
2000 rows affected
17000 rows affected
 hidden batch(es)


EXPLAIN ANALYZE SELECT DISTINCT ON (1) n.number, p.code FROM num n JOIN prefix p ON right(n.number, -1) LIKE (p.code || '%') ORDER BY n.number, p.code DESC;
QUERY PLAN
Unique (cost=31957.90..32807.90 rows=16999 width=14) (actual time=82.192..82.585 rows=1771 loops=1)
-> Sort (cost=31957.90..32382.90 rows=170000 width=14) (actual time=82.191..82.287 rows=1817 loops=1)
Sort Key: n.number, p.code DESC
Sort Method: quicksort Memory: 134kB
-> Nested Loop (cost=0.79..14280.50 rows=170000 width=14) (actual time=0.151..79.255 rows=1817 loops=1)
-> Seq Scan on prefix p (cost=0.00..31.00 rows=2000 width=5) (actual time=0.015..0.645 rows=2000 loops=1)
-> Bitmap Heap Scan on num n (cost=0.79..6.27 rows=85 width=9) (actual time=0.035..0.036 rows=1 loops=2000)
Recheck Cond: ("right"(number, '-1'::integer) ~~ (p.code || '%'::text))
Rows Removed by Index Recheck: 0
Heap Blocks: exact=1495
-> Bitmap Index Scan on num_trgm_gin_idx (cost=0.00..0.77 rows=85 width=0) (actual time=0.033..0.033 rows=1 loops=2000)
Index Cond: ("right"(number, '-1'::integer) ~~ (p.code || '%'::text))
Planning time: 0.428 ms
Execution time: 82.713 ms
 hidden batch(es)