Ps 1785 person teams relation

[gburgett]

fixes https://github.com/watermarkchurch/paper-signs/issues/1785

The issue was doing nested joins where the link is in an array - this ended up being a little tricky. I had to create a postgres function that works as a splat operator. This is inefficient, as the query planner shows, so the first layer of joining happens on the indexed "links" column, and then applying the splat operator over the arrays will only be applied afterwards.

[gburgett]

Raw EXPLAIN ANALYZE output:

"Nested Loop  (cost=5.30..1935.37 rows=1 width=968) (actual time=8.072..8.198 rows=2 loops=1)"
"  Join Filter: (((s0.id)::text = ANY (t.links)) AND (SubPlan 1))"
"  Rows Removed by Join Filter: 130"
"  ->  Bitmap Heap Scan on contentful_raw t  (cost=5.30..303.34 rows=132 width=968) (actual time=0.042..0.114 rows=132 loops=1)"
"        Recheck Cond: (((((data -> 'sys'::text) -> 'contentType'::text) -> 'sys'::text) ->> 'id'::text) = 'team'::text)"
"        Heap Blocks: exact=92"
"        ->  Bitmap Index Scan on contentful_raw_value_content_type  (cost=0.00..5.27 rows=132 width=0) (actual time=0.029..0.029 rows=154 loops=1)"
"              Index Cond: (((((data -> 'sys'::text) -> 'contentType'::text) -> 'sys'::text) ->> 'id'::text) = 'team'::text)"
"  ->  Materialize  (cost=0.00..557.56 rows=4 width=22) (actual time=0.015..0.060 rows=1 loops=132)"
"        ->  Seq Scan on contentful_raw s0  (cost=0.00..557.54 rows=4 width=22) (actual time=1.940..7.842 rows=1 loops=1)"
"              Filter: ((((data -> 'fields'::text) -> 'lastName'::text) -> 'en-US'::text) ? 'Burgett'::text)"
"              Rows Removed by Filter: 3776"
"  SubPlan 1"
"    ->  Function Scan on jsonb_array_elements link  (cost=0.26..2.01 rows=1 width=0) (actual time=0.078..0.078 rows=1 loops=2)"
"          Filter: (((value -> 'sys'::text) -> 'id'::text) ? (s0.id)::text)"
"          Rows Removed by Filter: 21"
"Planning time: 0.162 ms"
"Execution time: 8.239 ms"

[watermarkchurch-bot]

	2 Warnings
:warning:	This is a big pull request - please break it up into smaller units of work
:warning:	No issue referenced - please create an issue describing a single unit of work and reference it using “closes #[the issue number]”

Generated by :no_entry_sign: Danger

[coveralls]

Coverage remained the same at 94.183% when pulling 92a990f478f90706d0b43430eb7593e36269fe27 on ps_1785_person_teams_relation into 8623be068894015d1d0a5110d66183196211ab48 on master.

[gburgett]

If we want to speed up this operation (cause 8ms is a lot if it's something we do a ton of, like parent page queries in WMR) then we can create a GIN index:

CREATE INDEX IF NOT EXISTS contentful_raw_field_lastName_gin ON contentful_raw USING GIN ((data->'fields'->'lastName'->'en-US') jsonb_ops);

Here's the analyze:

"Nested Loop  (cost=13.33..1400.85 rows=1 width=968) (actual time=0.235..0.392 rows=2 loops=1)"
"  Join Filter: (((s0.id)::text = ANY (t.links)) AND (SubPlan 1))"
"  Rows Removed by Join Filter: 130"
"  ->  Bitmap Heap Scan on contentful_raw t  (cost=5.30..303.34 rows=132 width=968) (actual time=0.042..0.118 rows=132 loops=1)"
"        Recheck Cond: (((((data -> 'sys'::text) -> 'contentType'::text) -> 'sys'::text) ->> 'id'::text) = 'team'::text)"
"        Heap Blocks: exact=92"
"        ->  Bitmap Index Scan on contentful_raw_value_content_type  (cost=0.00..5.27 rows=132 width=0) (actual time=0.030..0.030 rows=154 loops=1)"
"              Index Cond: (((((data -> 'sys'::text) -> 'contentType'::text) -> 'sys'::text) ->> 'id'::text) = 'team'::text)"
"  ->  Materialize  (cost=8.03..23.04 rows=4 width=22) (actual time=0.000..0.000 rows=1 loops=132)"
"        ->  Bitmap Heap Scan on contentful_raw s0  (cost=8.03..23.02 rows=4 width=22) (actual time=0.011..0.011 rows=1 loops=1)"
"              Recheck Cond: ((((data -> 'fields'::text) -> 'lastName'::text) -> 'en-US'::text) ? 'Burgett'::text)"
"              Heap Blocks: exact=1"
"              ->  Bitmap Index Scan on contentful_raw_field_lastname_gin  (cost=0.00..8.03 rows=4 width=0) (actual time=0.006..0.006 rows=1 loops=1)"
"                    Index Cond: ((((data -> 'fields'::text) -> 'lastName'::text) -> 'en-US'::text) ? 'Burgett'::text)"
"  SubPlan 1"
"    ->  Function Scan on jsonb_array_elements link  (cost=0.26..2.01 rows=1 width=0) (actual time=0.089..0.089 rows=1 loops=2)"
"          Filter: (((value -> 'sys'::text) -> 'id'::text) ? (s0.id)::text)"
"          Rows Removed by Filter: 21"
"Planning time: 0.197 ms"
"Execution time: 0.437 ms"