Postgres 12

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?.

select version();

CREATE TABLE public.emails (
  id                bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    (MAXVALUE 9223372036854775807),
  sender            jsonb NOT NULL,
  reporter      jsonb not null
);

insert into emails (sender, reporter) VALUES ('[{"email": "dennis1@example.com", "name": "dennis1"}]', '[]');
insert into emails (sender, reporter) VALUES ('[{"email": "dennis2@example.com", "name": "dennis1"}]', '[{"email": "john@example.com", "name": "john"}, {"email": "dennis1@example.com", "name": "dennis1"}, {"email": "dennis2@example.com", "name": "dennis2"}]');
insert into emails (sender, reporter) VALUES ('[{"email": "dennis1@example.com", "name": "dennis1"}]', '[]');
insert into emails (sender, reporter) VALUES ('[{"email": "dennis1@example.com", "name": "dennis1"}]', '[]');

SELECT
*
FROM "emails" AS "e"
WHERE (EXISTS (SELECT
    *
  FROM JSONB_ARRAY_ELEMENTS_TEXT("e"."sender") AS "e" ("email")
  WHERE ("e"."email" ~* '' or "e"."name" ~* ''))
);

-- OR -- (ı want to search `sender` AND `reporter` AT THE SAME TIME)

SELECT
*
FROM "emails" AS "e"
WHERE (EXISTS (SELECT
    *
  FROM JSONB_ARRAY_ELEMENTS_TEXT("e"."reporter") AS "e" ("email")
  WHERE ("e"."email" ~* 'john' or "e"."name" ~* 'john'))
)

version
PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit

1 rows affected

1 rows affected

1 rows affected

1 rows affected

id	sender	reporter
1	[{"name": "dennis1", "email": "dennis1@example.com"}]	[]
2	[{"name": "dennis1", "email": "dennis2@example.com"}]	[{"name": "john", "email": "john@example.com"}, {"name": "dennis1", "email": "dennis1@example.com"}, {"name": "dennis2", "email": "dennis2@example.com"}]
3	[{"name": "dennis1", "email": "dennis1@example.com"}]	[]
4	[{"name": "dennis1", "email": "dennis1@example.com"}]	[]

id	sender	reporter
2	[{"name": "dennis1", "email": "dennis2@example.com"}]	[{"name": "john", "email": "john@example.com"}, {"name": "dennis1", "email": "dennis1@example.com"}, {"name": "dennis2", "email": "dennis2@example.com"}]

name	email
dennis1	dennis1@example.com
dennis1	dennis2@example.com
john	john@example.com