Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE ref_name (
  ref_name_id   serial PRIMARY KEY
, ref_name      text
, ref_name_type text
, name_display  text
, load_date     timestamp
);
   
INSERT INTO ref_name
SELECT nextval('ref_name_ref_name_id_seq')::int
     , g.ref_name
     , CASE WHEN random() > .8 THEN 'E' ELSE 'X' END
     , 'A 123 does not matter'
     , '2014-11-14 00:00:00'::timestamp
FROM  (
  SELECT string_agg(chr((random() * 15)::int + 64), '') AS ref_name  -- basic chars
  FROM   generate_series(1,1600000) g
  GROUP  BY (random()* 40000)::int   -- around 40 characters
  ) g;

INSERT INTO ref_name
VALUES
  (1652632, 'A 123 SYSTEMS', 'E', 'A 123 SYSTEMS INC', '2014-11-14 00:00:00')
, (1652633, 'A123 SYSTEMS', 'E', 'A123 SYSTEMS INC', '2014-11-14 00:00:00')
, (1652640, 'A 1 ACCOUSTICS', 'E', 'A-1 ACCOUSTICS', '2014-11-14 00:00:00')
, (1652641, 'A 1 ACOUSTICS', 'E', 'A-1 ACOUSTICS', '2014-11-14 00:00:00')
, (1652642, 'A1 ACOUSTICS', 'E', 'A1 ACOUSTICS INC', '2014-11-14 00:00:00')
, (1652650, 'A 1 A ELECTRICAL', 'E', 'A-1 A ELECTRICAL INC', '2014-11-14 00:00:00')
, (1652651, 'A 1 A ELECTRICIAN', 'E', 'A 1 A ELECTRICIAN INC', '2014-11-14 00:00:00')
, (1652652, 'A 1A ELECTRICIAN', 'E', 'A 1A ELECTRICIAN INC', '2014-11-14 00:00:00')
, (1652653, 'A1 A ELECTRICIAN', 'E', 'A1 A ELECTRICIAN INC', '2014-11-14 00:00:00')
, (1691270, 'ALBERT GARLATTI', 'E', 'ALBERT GARLATTI', '2014-11-14 00:00:00')
, (1691271, 'ALBERT GARLATTI CONSTRUCTION', 'E', 'ALBERT GARLATTI CONSTRUCTION CO', '2014-11-14 00:00:00')
, (1680892, 'AG HOG PITTSBURGH', 'E', 'AG-HOG PITTSBURGH CO INC', '2014-11-14 00:00:00')
, (1680893, 'AGHOG PITTSBURGH', 'E', 'AGHOG PITTSBURGH CO', '2014-11-14 00:00:00')
, (1680928, 'AGILE PURSUITS FRACHISING', 'E', 'AGILE PURSUITS FRACHISING INC', '2014-11-14 00:00:00')
, (1680929, 'AGILE PURSUITS FRANCHISING', 'E', 'AGILE PURSUITS FRANCHISING INC', '2014-11-14 00:00:00')
, (1680956, 'AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT', 'E', 'AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT', '2014-11-14 00:00:00')
, (1680957, 'AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI', 'E', 'AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI', '2014-11-14 00:00:00')
;

CREATE INDEX ref_name_trgm_gist_idx ON ref_name USING gist (ref_name gist_trgm_ops)
WHERE ref_name_type = 'E';

CREATE INDEX ref_name_trgm_gin_idx ON ref_name USING gin (ref_name gin_trgm_ops)
WHERE ref_name_type = 'E';

CREATE INDEX ref_name_pattern_ops_idx ON ref_name (ref_name text_pattern_ops)
WHERE ref_name_type = 'E';

CREATE TABLE



INSERT 0 40001



INSERT 0 17



CREATE INDEX



CREATE INDEX



CREATE INDEX


SELECT set_limit(0.8);


set_limit


0.8



SELECT 1


-- Superior solution in pg 9.3+: LATERAL
SELECT a.ref_name_id
     , a.ref_name
     , a.name_display
     , b.ref_name_id  AS match_name_id
     , b.ref_name     AS match_name
     , b.name_display AS match_name_display
FROM   ref_name a
CROSS  JOIN LATERAL (
   SELECT b.ref_name_id, b.ref_name, b.name_display
   FROM   ref_name b
   WHERE  b.ref_name ~~ 'A%'
   AND    b.ref_name_type = 'E'
   AND    a.ref_name_id < b.ref_name_id
   AND    a.ref_name % b.ref_name
   ORDER  BY a.ref_name <-> b.ref_name
   LIMIT  1                                -- max. 1 best match
   ) b
WHERE  a.ref_name ~~ 'A%'
AND    a.ref_name_type = 'E'
ORDER  BY 1;

-- We can return many matches per row
SELECT a.ref_name_id
     , a.ref_name
     , a.name_display
     , b.ref_name_id  AS match_name_id
     , b.ref_name     AS match_name
     , b.name_display AS match_name_display
FROM   ref_name a
CROSS  JOIN LATERAL (
   SELECT b.ref_name_id, b.ref_name, b.name_display
   FROM   ref_name b
   WHERE  b.ref_name ~~ 'A%'
   AND    b.ref_name_type = 'E'
   AND    a.ref_name_id < b.ref_name_id
   AND    a.ref_name % b.ref_name
   ORDER  BY a.ref_name <-> b.ref_name
   LIMIT  10                                -- max. 10 best matches
   ) b
WHERE  a.ref_name ~~ 'A%'
AND    a.ref_name_type = 'E'
ORDER  BY 1;

-- All rows allowed as match (not just starting with A)
SELECT a.ref_name_id
     , a.ref_name
     , a.name_display
     , b.ref_name_id  AS match_name_id
     , b.ref_name     AS match_name
     , b.name_display AS match_name_display
FROM   ref_name a
CROSS  JOIN LATERAL (
   SELECT b.ref_name_id, b.ref_name, b.name_display
   FROM   ref_name b
   WHERE  b.ref_name_type = 'E'
-- AND    ref_name ~~ 'A%'   -- Find best match among *all* rows
   AND    a.ref_name_id < b.ref_name_id
   AND    a.ref_name % b.ref_name
   ORDER  BY a.ref_name <-> b.ref_name
   LIMIT  5                                 -- max. 5 best matches
   ) b
WHERE  a.ref_name ~~ 'A%'
AND    a.ref_name_type = 'E'
ORDER  BY 1;


ref_name_id
ref_name
name_display
match_name_id
match_name
match_name_display


1652640
A 1 ACCOUSTICS
A-1 ACCOUSTICS
1652641
A 1 ACOUSTICS
A-1 ACOUSTICS

1652651
A 1 A ELECTRICIAN
A 1 A ELECTRICIAN INC
1652652
A 1A ELECTRICIAN
A 1A ELECTRICIAN INC

1680928
AGILE PURSUITS FRACHISING
AGILE PURSUITS FRACHISING INC
1680929
AGILE PURSUITS FRANCHISING
AGILE PURSUITS FRANCHISING INC

1680956
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
1680957
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI



SELECT 4




ref_name_id
ref_name
name_display
match_name_id
match_name
match_name_display


1652640
A 1 ACCOUSTICS
A-1 ACCOUSTICS
1652641
A 1 ACOUSTICS
A-1 ACOUSTICS

1652651
A 1 A ELECTRICIAN
A 1 A ELECTRICIAN INC
1652652
A 1A ELECTRICIAN
A 1A ELECTRICIAN INC

1680928
AGILE PURSUITS FRACHISING
AGILE PURSUITS FRACHISING INC
1680929
AGILE PURSUITS FRANCHISING
AGILE PURSUITS FRANCHISING INC

1680956
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
1680957
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI



SELECT 4




ref_name_id
ref_name
name_display
match_name_id
match_name
match_name_display


1652640
A 1 ACCOUSTICS
A-1 ACCOUSTICS
1652641
A 1 ACOUSTICS
A-1 ACOUSTICS

1652651
A 1 A ELECTRICIAN
A 1 A ELECTRICIAN INC
1652652
A 1A ELECTRICIAN
A 1A ELECTRICIAN INC

1680928
AGILE PURSUITS FRACHISING
AGILE PURSUITS FRACHISING INC
1680929
AGILE PURSUITS FRANCHISING
AGILE PURSUITS FRANCHISING INC

1680956
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
1680957
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI



SELECT 4


-- work around missing LATERAL in Postgres 9.2 with subquery
SELECT a.*
     , b.ref_name     AS match_name
     , b.name_display AS match_name_display
FROM  (
   SELECT ref_name_id
        , ref_name
        , name_display
        , (SELECT ref_name_id AS match_name_id
           FROM   ref_name b
           WHERE  ref_name_type = 'E'
           AND    ref_name ~~ 'A%'
           AND    ref_name_id > a.ref_name_id
           AND    ref_name % a.ref_name
           ORDER  BY ref_name <-> a.ref_name
           LIMIT  1                                -- max. 1 best match
          )
   FROM   ref_name a
   WHERE  ref_name ~~ 'A%'
   AND    ref_name_type = 'E'
   ) a
JOIN   ref_name b ON b.ref_name_id = a.match_name_id
ORDER  BY 1;

-- Alternative with whole rows in pg 9.2+ (probably slower)
SELECT ref_name_id, ref_name, name_display
     , (b).ref_name_id  AS match_name_id
     , (b).ref_name     AS match_name
     , (b).name_display AS match_name_display
FROM  (
   SELECT ref_name_id
        , ref_name
        , name_display
        , (SELECT b
           FROM   ref_name b
           WHERE  ref_name_type = 'E'
           AND    ref_name ~~ 'A%'
           AND    ref_name_id > a.ref_name_id
           AND    ref_name % a.ref_name
           ORDER  BY ref_name <-> a.ref_name
           LIMIT  1                                -- max. 1 best match
          )
   FROM   ref_name a
   WHERE  ref_name ~~ 'A%'
   AND    ref_name_type = 'E'
   ) a
WHERE  b IS NOT NULL
ORDER  BY 1;

-- Cheaper with composed row type in pg 9.2
SELECT *
FROM  (
   SELECT ref_name_id
        , ref_name
        , name_display
        , (SELECT (ref_name_id, ref_name, name_display) AS match
           FROM   ref_name b
           WHERE  ref_name_type = 'E'
           AND    ref_name ~~ 'A%'
           AND    ref_name_id > a.ref_name_id
           AND    ref_name % a.ref_name
           ORDER  BY ref_name <-> a.ref_name
           LIMIT  1                                -- max. 1 best match
          )
   FROM   ref_name a
   WHERE  ref_name ~~ 'A%'
   AND    ref_name_type = 'E'
   ) a
WHERE  match IS NOT NULL
ORDER  BY 1;


ref_name_id
ref_name
name_display
match_name_id
match_name
match_name_display


1652640
A 1 ACCOUSTICS
A-1 ACCOUSTICS
1652641
A 1 ACOUSTICS
A-1 ACOUSTICS

1652651
A 1 A ELECTRICIAN
A 1 A ELECTRICIAN INC
1652652
A 1A ELECTRICIAN
A 1A ELECTRICIAN INC

1680928
AGILE PURSUITS FRACHISING
AGILE PURSUITS FRACHISING INC
1680929
AGILE PURSUITS FRANCHISING
AGILE PURSUITS FRANCHISING INC

1680956
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
1680957
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI



SELECT 4




ref_name_id
ref_name
name_display
match_name_id
match_name
match_name_display


1652640
A 1 ACCOUSTICS
A-1 ACCOUSTICS
1652641
A 1 ACOUSTICS
A-1 ACOUSTICS

1652651
A 1 A ELECTRICIAN
A 1 A ELECTRICIAN INC
1652652
A 1A ELECTRICIAN
A 1A ELECTRICIAN INC

1680928
AGILE PURSUITS FRACHISING
AGILE PURSUITS FRACHISING INC
1680929
AGILE PURSUITS FRANCHISING
AGILE PURSUITS FRANCHISING INC

1680956
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
1680957
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI



SELECT 4




ref_name_id
ref_name
name_display
match


1652640
A 1 ACCOUSTICS
A-1 ACCOUSTICS
(1652641,"A 1 ACOUSTICS","A-1 ACOUSTICS")

1652651
A 1 A ELECTRICIAN
A 1 A ELECTRICIAN INC
(1652652,"A 1A ELECTRICIAN","A 1A ELECTRICIAN INC")

1680928
AGILE PURSUITS FRACHISING
AGILE PURSUITS FRACHISING INC
(1680929,"AGILE PURSUITS FRANCHISING","AGILE PURSUITS FRANCHISING INC")

1680956
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
(1680957,"AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI","AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI")



SELECT 4


-- your original query  optimized           -- all matches
SELECT a.ref_name_id,a.ref_name, a.name_display
     , b.ref_name_id AS match_name_id, b.ref_name AS match_name, b.name_display AS match_name_display
FROM   ref_name a
JOIN   ref_name b ON a.ref_name % b.ref_name
                 AND a.ref_name_id > b.ref_name_id
WHERE  a.ref_name ~>=~ 'A'
AND    a.ref_name ~<~  'B'
AND    a.ref_name_type = 'E'
AND    b.ref_name ~>=~ 'A'
AND    b.ref_name ~<~  'B'
AND    b.ref_name_type = 'E'
ORDER  BY 1;

-- original query
SELECT a.ref_name_id as name_id,a.ref_name AS name,
  a.name_display AS name_display,b.ref_name_id AS matched_name_id,
  b.ref_name AS matched_name,b.name_display AS matched_name_display
FROM ref_name a
JOIN ref_name b
 ON a.ref_name_id<>b.ref_name_id
 AND a.ref_name_id>b.ref_name_id
 AND a.ref_name % b.ref_name
WHERE a.ref_name ~>=~ 'A' and a.ref_name ~<~'B'
 AND b.ref_name ~>=~ 'A' and b.ref_name ~<~'B'
 AND a.ref_name_type='E'
 AND b.ref_name_type='E'
 ORDER  BY 1;


ref_name_id
ref_name
name_display
match_name_id
match_name
match_name_display


1652641
A 1 ACOUSTICS
A-1 ACOUSTICS
1652640
A 1 ACCOUSTICS
A-1 ACCOUSTICS

1652652
A 1A ELECTRICIAN
A 1A ELECTRICIAN INC
1652651
A 1 A ELECTRICIAN
A 1 A ELECTRICIAN INC

1680929
AGILE PURSUITS FRANCHISING
AGILE PURSUITS FRANCHISING INC
1680928
AGILE PURSUITS FRACHISING
AGILE PURSUITS FRACHISING INC

1680957
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI
1680956
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT



SELECT 4




name_id
name
name_display
matched_name_id
matched_name
matched_name_display


1652641
A 1 ACOUSTICS
A-1 ACOUSTICS
1652640
A 1 ACCOUSTICS
A-1 ACCOUSTICS

1652652
A 1A ELECTRICIAN
A 1A ELECTRICIAN INC
1652651
A 1 A ELECTRICIAN
A 1 A ELECTRICIAN INC

1680929
AGILE PURSUITS FRANCHISING
AGILE PURSUITS FRANCHISING INC
1680928
AGILE PURSUITS FRACHISING
AGILE PURSUITS FRACHISING INC

1680957
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI
1680956
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT
AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT



SELECT 4
ref_name_id	ref_name	name_display	match_name_id	match_name	match_name_display
1652640	A 1 ACCOUSTICS	A-1 ACCOUSTICS	1652641	A 1 ACOUSTICS	A-1 ACOUSTICS
1652651	A 1 A ELECTRICIAN	A 1 A ELECTRICIAN INC	1652652	A 1A ELECTRICIAN	A 1A ELECTRICIAN INC
1680928	AGILE PURSUITS FRACHISING	AGILE PURSUITS FRACHISING INC	1680929	AGILE PURSUITS FRANCHISING	AGILE PURSUITS FRANCHISING INC
1680956	AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT	AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT	1680957	AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI	AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI