Postgres 14

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 word_mash_dictionary (
  reference serial
, word      text
, spelling  text[]
);

INSERT INTO word_mash_dictionary(word, spelling)
SELECT word, string_to_array(word, null)
FROM  (
   VALUES
     ('any')
   , ('annoy')
   , ('no')
   , ('an')
   , ('toy')
   , ('annoying')
   , ('annoyance')
   , ('n')
   , ('nn')
   , ('nnn')
   ) t(word);

CREATE INDEX ON word_mash_dictionary USING gin (spelling);

CREATE TABLE



INSERT 0 10



CREATE INDEX


-- Fast matching with index-support, but false positives for repeated characters
SELECT reference, word, spelling
FROM   word_mash_dictionary
WHERE  spelling <@ '{a,n,o,y,t}'::text[];


reference
word
spelling


1
any
{a,n,y}

2
annoy
{a,n,n,o,y}

3
no
{n,o}

4
an
{a,n}

5
toy
{t,o,y}

8
n
{n}

9
nn
{n,n}

10
nnn
{n,n,n}



SELECT 8


-- False positive
SELECT '{n,n,n}'::text[] <@ '{a,n,o,y,t}'::text[];

-- Correct results
SELECT NOT EXISTS (
   SELECT unnest ('{n, a, t}'::text[])
   EXCEPT ALL
   SELECT unnest ('{a,n,o,y,t}'::text[])
   );

SELECT NOT EXISTS (
   SELECT unnest ('{n, n}'::text[])
   EXCEPT ALL
   SELECT unnest ('{a,n,o,y,t}'::text[])
   );


?column?


t



SELECT 1




?column?


t



SELECT 1




?column?


f



SELECT 1


-- Function wrapper:
CREATE OR REPLACE FUNCTION f_arr_is_contained(arr1 text[], arr2 text[])
  RETURNS bool
  LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
DECLARE
BEGIN
   PERFORM unnest (arr1)
   EXCEPT ALL
   SELECT unnest (arr2);

   RETURN NOT FOUND;
END
$func$;

CREATE FUNCTION


SELECT f_arr_is_contained(spelling, '{a,n,o,y,t}'::text[]) AS exact
     , spelling <@ '{a,n,o,y,t}'::text[] AS fast_with_false_positives
     , *
FROM   word_mash_dictionary
ORDER  BY 1, 2;


exact
fast_with_false_positives
reference
word
spelling


f
f
7
annoyance
{a,n,n,o,y,a,n,c,e}

f
f
6
annoying
{a,n,n,o,y,i,n,g}

f
t
10
nnn
{n,n,n}

f
t
2
annoy
{a,n,n,o,y}

f
t
9
nn
{n,n}

t
t
4
an
{a,n}

t
t
3
no
{n,o}

t
t
8
n
{n}

t
t
1
any
{a,n,y}

t
t
5
toy
{t,o,y}



SELECT 10


-- Fast *and* exact:
SELECT reference, word, spelling
FROM   word_mash_dictionary
WHERE  spelling <@ '{a,n,o,y,t}'::text[]
AND    f_arr_is_contained(spelling, '{a,n,o,y,t}'::text[]);


reference
word
spelling


1
any
{a,n,y}

3
no
{n,o}

4
an
{a,n}

5
toy
{t,o,y}

8
n
{n}



SELECT 5


-- See: index can be used
SET enable_seqscan = off;

EXPLAIN
SELECT reference, word, spelling
FROM   word_mash_dictionary
WHERE  spelling <@ '{a,n,o,y,t}'::text[]
AND    f_arr_is_contained(spelling, '{a,n,o,y,t}'::text[]);

SET




QUERY PLAN


Bitmap Heap Scan on word_mash_dictionary  (cost=28.00..32.26 rows=1 width=68)

  Recheck Cond: (spelling <@ '{a,n,o,y,t}'::text[])

  Filter: f_arr_is_contained(spelling, '{a,n,o,y,t}'::text[])

  ->  Bitmap Index Scan on word_mash_dictionary_spelling_idx  (cost=0.00..28.00 rows=1 width=0)

        Index Cond: (spelling <@ '{a,n,o,y,t}'::text[])



EXPLAIN

reference	word	spelling
1	any	{a,n,y}
2	annoy	{a,n,n,o,y}
3	no	{n,o}
4	an	{a,n}
5	toy	{t,o,y}
8	n	{n}
9	nn	{n,n}
10	nnn	{n,n,n}

exact	fast_with_false_positives	reference	word	spelling
f	f	7	annoyance	{a,n,n,o,y,a,n,c,e}
f	f	6	annoying	{a,n,n,o,y,i,n,g}
f	t	10	nnn	{n,n,n}
f	t	2	annoy	{a,n,n,o,y}
f	t	9	nn	{n,n}
t	t	4	an	{a,n}
t	t	3	no	{n,o}
t	t	8	n	{n}
t	t	1	any	{a,n,y}
t	t	5	toy	{t,o,y}