product-os / autumndb Goto Github PK

View Code? Open in Web Editor NEW

4.0 4.0 3.0 10.57 MB

JSON schema + Postgres

License: Apache License 2.0

JavaScript 0.68% Dockerfile 0.02% TypeScript 99.30%

graph-database json-schema postgres

autumndb's People

Contributors

Stargazers

Watchers

Forkers

jsfix-ci lucianbuzzo karaxuna

autumndb's Issues

Not possible to change the version of a contract

Changing the version of a contract currently fails with an error. This seems to be due to the upsert implementation down the call chain.

We either need to fix this or explicitly disallow it. But for Transformers we expected so far to be able to "merge" versions from their draft versions by simply changing the version.

CVE-2021-23358 (High) detected in underscore-1.10.2.tgz - autoclosed

CVE-2021-23358 - High Severity Vulnerability

Vulnerable Library - underscore-1.10.2.tgz

JavaScript's functional programming helper library.

Library home page: https://registry.npmjs.org/underscore/-/underscore-1.10.2.tgz

Dependency Hierarchy:

typed-errors-1.1.0.tgz (Root Library)
- ❌ underscore-1.10.2.tgz (Vulnerable Library)

Found in base branch: master

Vulnerability Details

The package underscore from 1.13.0-0 and before 1.13.0-2, from 1.3.2 and before 1.12.1 are vulnerable to Arbitrary Code Injection via the template function, particularly when a variable property is passed as an argument as it is not sanitized.

Publish Date: 2021-03-29

URL: CVE-2021-23358

CVSS 3 Score Details (7.2)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Network
- Attack Complexity: Low
- Privileges Required: High
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: High
- Integrity Impact: High
- Availability Impact: High

For more information on CVSS3 Scores, click here.

Suggested Fix

Type: Upgrade version

Origin: https://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2021-23358

Release Date: 2021-03-29

Fix Resolution: underscore - 1.12.1,1.13.0-2

Step up your Open Source Security Game with WhiteSource here

CVE-2021-23337 (High) detected in lodash-4.17.20.tgz - autoclosed

CVE-2021-23337 - High Severity Vulnerability

Vulnerable Library - lodash-4.17.20.tgz

Lodash modular utilities.

Library home page: https://registry.npmjs.org/lodash/-/lodash-4.17.20.tgz

Dependency Hierarchy:

❌ lodash-4.17.20.tgz (Vulnerable Library)

Found in HEAD commit: 9c508fbbab629dad8348bf09ae4bd73e0ec6500f

Found in base branch: master

Vulnerability Details

All versions of package lodash; all versions of package org.fujion.webjars:lodash are vulnerable to Command Injection via template.

Publish Date: 2021-02-15

URL: CVE-2021-23337

CVSS 3 Score Details (7.2)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Network
- Attack Complexity: Low
- Privileges Required: High
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: High
- Integrity Impact: High
- Availability Impact: High

For more information on CVSS3 Scores, click here.

Step up your Open Source Security Game with WhiteSource here

Add documentation

Need to generate proper documentation and add to repo. Could do this with jsdoc2md, but would probably be better to wait to use TypeDoc when converting to TypeScript.

Action Required: Fix Renovate Configuration

There is an error with this repository's Renovate configuration that needs to be fixed. As a precaution, Renovate will stop PRs until it is resolved.

Error type: Cannot find preset's package (github>product-os/jellyfish-renovate-config)

Should the docs link work?

Found your project today and thought I'd take a look at the docs.

Tried the link in the readme:

https://product-os.github.io/autumndb

...and it goes to a 404 page. Maybe a permissions issue?

Partial Index Utilization Checks

support-thread__data_status__idx

Query:

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'status' ],
      properties: {
        status: {
          type: 'string',
          const: 'open'
        }
      }
    }
  }
})

Compiled SQL:

EXPLAIN ANALYZE SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"status"}') = 'string' AND (cards.data#>>'{"status"}')::text = 'open') LIMIT 1000;

EXPLAIN ANALYZE Results:

jellyfish=> EXPLAIN ANALYZE SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"status"}') = 'string' AND (cards.data#>>'{"status"}')::text = 'open') LIMIT 1000;
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..9.12 rows=1 width=32) (actual time=0.116..23.981 rows=800 loops=1)
   ->  Index Scan using "support-thread__data_status__idx" on cards  (cost=0.29..9.12 rows=1 width=32) (actual time=0.115..23.902 rows=800 loops=1)
         Index Cond: ((data #>> '{status}'::text[]) = 'open'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.002..0.002 rows=0 loops=800)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                 Rows Removed by Filter: 0
 Planning Time: 6.985 ms
 Execution Time: 24.060 ms
(10 rows)

message__data_readBy__idx

Query:

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'readBy' ],
      properties: {
        readBy: {
          type: 'array',
          contains: {
            const: 'test'
          }
        }
      }
    }
  }
})

Compiled SQL:

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"readBy"}') = 'array' AND cards.data#>'{"readBy"}' @> '"test"') LIMIT 1000

EXPLAIN ANALYZE Results:

jellyfish=> EXPLAIN ANALYZE SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"readBy"}') = 'array' AND cards.data#>'{"readBy"}' @> '"test"') LIMIT 1000;
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=84.23..518.01 rows=1 width=32) (actual time=1.176..1.177 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=84.23..518.01 rows=1 width=32) (actual time=1.176..1.176 rows=0 loops=1)
         Recheck Cond: (((data #> '{readBy}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{readBy}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "message__data_readBy__idx"  (cost=0.00..84.22 rows=377 width=0) (actual time=1.174..1.174 rows=0 loops=1)
               Index Cond: ((data #> '{readBy}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.232 ms
 Execution Time: 1.209 ms
(11 rows)

CVE-2021-29469 (Medium) detected in redis-3.1.0.tgz - autoclosed

CVE-2021-29469 - Medium Severity Vulnerability

Vulnerable Library - redis-3.1.0.tgz

A high performance Redis client.

Library home page: https://registry.npmjs.org/redis/-/redis-3.1.0.tgz

Dependency Hierarchy:

❌ redis-3.1.0.tgz (Vulnerable Library)

Found in base branch: master

Vulnerability Details

Node-redis is a Node.js Redis client. Before version 3.1.1, when a client is in monitoring mode, the regex begin used to detected monitor messages could cause exponential backtracking on some strings. This issue could lead to a denial of service. The issue is patched in version 3.1.1.

Publish Date: 2021-04-23

URL: CVE-2021-29469

CVSS 3 Score Details (5.3)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Network
- Attack Complexity: High
- Privileges Required: Low
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: None
- Integrity Impact: None
- Availability Impact: High

For more information on CVSS3 Scores, click here.

Suggested Fix

Type: Upgrade version

Origin: GHSA-35q2-47q7-3pc3

Release Date: 2021-04-23

Fix Resolution: redis - 3.1.1

Step up your Open Source Security Game with WhiteSource here

Properties returned for the same link type in different `$$links` is undefined

The following schema:

{
 anyOf: [
  {
   $$links: {
    owns: {
     additionalProperties: true,
     properties: {
      slug: {
       const: "user-a"
      }
     }
    }
   }
  },
  {
   $$links: {
    owns: {
     additionalProperties: false,
     properties: {
      slug: {
       const: "user-b"
      }
     }
    }
   }
  }
 ]
}

Will always return the same properties regardless of what the slug is.

Remove unused (type) inices

We automatically create indices for types, but we never remove them, when a type is changed or deleted:
https://github.com/product-os/jellyfish-core/blob/d7e258116f1d8c0087fab4b6f68a5ebac242665e/lib/backend/postgres/index.js#L270-L284

JellyfishElementAlreadyExists is not returned to client but a 500 InternalServerError

When creating a contract with a ID that already exists in the system I expect to receive an 4xx error that contains the reason JellyfishElementAlreadyExists.

Actually this happens:

Check if it's practical to simplify the query structure with links using `links2`

The current query structure has multiple stages:

The innermost query fetches all paths in the graph that's being queried. Only the contract IDs are fetched in this step. Some limited OFFSET/LIMIT handling is also done here if required.
These paths are split and deduplicated into individual edges.
The edges are put inside a MATERIALIZED CTE to prevent PG from using some really bad query plans. Unfortunately there's no other way to create an optimization barrier that I know of.
The actual contract data is fetched and the link tree is reconstructed based on the edges stored previously. This step may fetch the same contract multiple times as it builds a tree and not a graph. ORDER BY, OFFSET, and LIMIT are handled here too.

This is the structure I arrived at after a lot of testing with large test DBs. "Obvious" methods like subqueries or lateral joins did significantly worse in the worst case, even if they might have been faster in some easier cases. Unfortunately, this structure also requires complex queries that can be hard to understand and debug. It is designed to "handle" postgres and keep it in the happy path.

Since moving to the links2 table and seeing how it performs compared to the previous table I've wondered if this complexity is still necessary nowadays. This is going to be a tracking issue for benchmarks and redesigns if alternatives shown to be desirable.

For each scenario, test the following variants:

Trivial match: all contracts have their ID specified. The result set is one with one link (if applicable). This is the easiest case and any method that doesn't do well here must be burned with fire.
Sequential: only the root contract has its ID specified. The result set is one with 10k links (if applicable). This case tests for the fairly common case where we query for a specific card and all its links.
Reverse: only the links have their ID specified. The result set is 10k contracts, each with the same single link (if applicable). This case tests for the not-so-common case where we query for all cards that have a specific link. This case can also be particularly hard for methods that hardcode the order of operations, like subqueries.
Aggregate match: no IDs are specified. The result set is 10k contracts, each with 10k links. This case is hard and tests for underspecified queries. Only applicable for the scenarios where the links exist.
Aggregate unmatch: no IDs are specified. The result set is empty. This is the hardest case and will cause plenty of churn but it is useful to test how well the query planner works with the query structure in the worst case (I've seen things....). Only applicable for the scenarios where the links don't exist.

For all queries, contracts have their types and one common and indexed data field specified. Both the root and the links have the same amount of random contracts but different types. The queries must return the the full contract as JSONB to prevent postgres from muddling the results with too much optimization.

While I don't think it's useful to test these right away, the method must be able to skip, limit and order both root cards and links.

For the tests I'll use postgres 13.4 with the default configuration and with the database stored in a ramdisk.

CVE-2019-10744 (High) detected in lodash-4.17.11.tgz

CVE-2019-10744 - High Severity Vulnerability

Vulnerable Library - lodash-4.17.11.tgz

Lodash modular utilities.

Library home page: https://registry.npmjs.org/lodash/-/lodash-4.17.11.tgz

Dependency Hierarchy:

@balena/jellyfish-logger-0.0.221.tgz (Root Library)
- le_node-1.8.0.tgz
  - ❌ lodash-4.17.11.tgz (Vulnerable Library)

Found in HEAD commit: cae9133bca33d1b1fa03ae812124e9b5e0262211

Found in base branch: master

Vulnerability Details

Versions of lodash lower than 4.17.12 are vulnerable to Prototype Pollution. The function defaultsDeep could be tricked into adding or modifying properties of Object.prototype using a constructor payload.

Publish Date: 2019-07-26

URL: CVE-2019-10744

CVSS 3 Score Details (9.8)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Network
- Attack Complexity: Low
- Privileges Required: None
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: High
- Integrity Impact: High
- Availability Impact: High

For more information on CVSS3 Scores, click here.

Suggested Fix

Type: Upgrade version

Origin: GHSA-jf85-cpcp-j695

Release Date: 2019-07-08

Fix Resolution: lodash-4.17.12, lodash-amd-4.17.12, lodash-es-4.17.12, lodash.defaultsdeep-4.6.1, lodash.merge- 4.6.2, lodash.mergewith-4.6.2, lodash.template-4.5.0

Step up your Open Source Security Game with WhiteSource here

WS-2020-0163 (Medium) detected in marked-0.8.2.tgz - autoclosed

WS-2020-0163 - Medium Severity Vulnerability

Vulnerable Library - marked-0.8.2.tgz

A markdown parser built for speed

Library home page: https://registry.npmjs.org/marked/-/marked-0.8.2.tgz

Dependency Hierarchy:

jsdoc-to-markdown-7.0.1.tgz (Root Library)
- jsdoc-api-7.0.0.tgz
  - jsdoc-3.6.6.tgz
    - ❌ marked-0.8.2.tgz (Vulnerable Library)

Found in HEAD commit: cae9133bca33d1b1fa03ae812124e9b5e0262211

Found in base branch: master

Vulnerability Details

marked before 1.1.1 is vulnerable to Regular Expression Denial of Service (REDoS). rules.js have multiple unused capture groups which can lead to a Denial of Service.

Publish Date: 2020-07-02

URL: WS-2020-0163

CVSS 3 Score Details (5.9)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Network
- Attack Complexity: High
- Privileges Required: None
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: None
- Integrity Impact: None
- Availability Impact: High

For more information on CVSS3 Scores, click here.

Suggested Fix

Type: Upgrade version

Origin: https://github.com/markedjs/marked/releases/tag/v1.1.1

Release Date: 2020-07-02

Fix Resolution: marked - 1.1.1

Step up your Open Source Security Game with WhiteSource here

Dependency Dashboard

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Rate-Limited

These updates are currently rate-limited. Click on a checkbox below to force their creation now.

Update github/codeql-action digest to 29b1f65
Update dependency json-schema-to-typescript to v13
🔐 Create all rate-limited PRs at once 🔐

Open

These updates have all been created already. Click a checkbox below to force a retry/rebase of any.

Detected dependencies

docker-compose

docker-compose.test.yml

docker-compose.yml

redis 7

dockerfile

Dockerfile

node 19

github-actions

.github/workflows/codeql-analysis.yml

actions/checkout v3@8e5e7e5ab8b370d6c329ec480221332ada57f0ab

github/codeql-action v2@f3feb00acb00f31a6f60280e6ace9ca31d91c76a

github/codeql-action v2@f3feb00acb00f31a6f60280e6ace9ca31d91c76a

github/codeql-action v2@f3feb00acb00f31a6f60280e6ace9ca31d91c76a

.github/workflows/flowzone.yml

npm

package.json

@balena/jellyfish-assert ^1.11.0

@balena/jellyfish-environment ^21.0.0

@balena/jellyfish-logger ^6.0.1

bluebird ^3.7.2

commander ^10.0.0

fast-equals ^5.0.1

fast-json-patch ^3.1.1

json-e ^4.5.1

json-schema ^0.4.0

json-schema-deref-sync ^0.14.0

json-schema-to-typescript ^12.0.0

lodash ^4.17.21

pg ^8.10.0

pg-format ^1.0.4

redis 4.6.6

redis-mock ^0.56.3

semver ^7.3.8

skhema ^6.0.6

stopword ^2.0.8

traverse ^0.6.7

typed-error ^3.2.1

uuid-v4-regex ^1.0.2

@balena/lint ^6.2.2

@json-schema-org/tests ^2.0.0

@types/bluebird ^3.5.38

@types/jest ^29.5.0

@types/json-schema ^7.0.11

@types/node ^18.15.11

@types/pg ^8.6.6

@types/pg-format ^1.0.2

@types/redis-mock ^0.17.1

@types/semver ^7.3.13

@types/stopword ^2.0.0

@types/traverse ^0.6.32

deplint ^1.1.3

jest ^29.5.0

lint-staged ^13.2.0

prettier ^2.8.7

rimraf ^5.0.0

simple-git-hooks ^2.8.1

ts-jest ^29.0.5

ts-node ^10.9.1

typedoc ^0.24.0

typescript ^5.0.3

node >=16.0.0

nvm

.nvmrc

node 18.15

regex

Dockerfile

node 19

docker-compose.yml

redis 7

Need postgres and redis instances in CI

Need to start Postgres and Redis instances during tests for integration tests to pass in balenaCI.

CVE-2020-7774 (High) detected in y18n-4.0.0.tgz

CVE-2020-7774 - High Severity Vulnerability

Vulnerable Library - y18n-4.0.0.tgz

the bare-bones internationalization library used by yargs

Library home page: https://registry.npmjs.org/y18n/-/y18n-4.0.0.tgz

Dependency Hierarchy:

deplint-1.1.2.tgz (Root Library)
- hawthorn-1.2.6.tgz
  - yargs-12.0.5.tgz
    - ❌ y18n-4.0.0.tgz (Vulnerable Library)

Found in HEAD commit: cae9133bca33d1b1fa03ae812124e9b5e0262211

Found in base branch: master

Vulnerability Details

This affects the package y18n before 3.2.2, 4.0.1 and 5.0.5. PoC by po6ix: const y18n = require('y18n')(); y18n.setLocale('proto'); y18n.updateLocale({polluted: true}); console.log(polluted); // true

Publish Date: 2020-11-17

URL: CVE-2020-7774

CVSS 3 Score Details (7.3)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Network
- Attack Complexity: Low
- Privileges Required: None
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: Low
- Integrity Impact: Low
- Availability Impact: Low

For more information on CVSS3 Scores, click here.

Suggested Fix

Type: Upgrade version

Origin: https://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2020-7774

Release Date: 2020-11-17

Fix Resolution: 5.0.5

Step up your Open Source Security Game with WhiteSource here

Fix type index definitions

We currently have a number of type indexes that do not match the generated SQL queries. Below is a list of type indexes with improper definitions, along with data suggesting that fixing them should improve query performance.

(All new test indexes referenced below were dropped immediately after testing was complete.)

Improperly defined type indexes

1. link__data_from_id__name__data_to_id__idx

Current definition

CREATE INDEX link__data_from_id__name__data_to_id__idx ON public.cards USING btree ((((data -> 'from'::text) ->> 'id'::text)), name, (((data -> 'to'::text) ->> 'id'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX link__data_from_id__name__data_to_id__idx ON public.cards USING btree (((data #>> '{from,id}'::text[])), name, ((data #>> '{to,id}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'name', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    name: {
      type: 'string',
      const: 'test'
    },
    data: {
      type: 'object',
      required: [ 'from', 'to' ],
      properties: {
        from: {
          type: 'object',
          required: [ 'id' ],
          properties: {
            id: {
              type: 'string',
              const: '1234'
            }
          }
        },
        to: {
          type: 'object',
          required: [ 'id' ],
          properties: {
            id: {
              type: 'string',
              const: '5678'
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND cards.name = 'test' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"from"}') = 'object' AND jsonb_typeof(cards.data#>'{"from", "id"}') = 'string' AND (cards.data#>>'{"from", "id"}')::text = '1234' AND jsonb_typeof(cards.data#>'{"to"}') = 'object' AND jsonb_typeof(cards.data#>'{"to", "id"}') = 'string' AND (cards.data#>>'{"to", "id"}')::text = '5678') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.56..946.23 rows=1 width=32) (actual time=0.017..0.018 rows=0 loops=1)
   ->  Index Scan using link__name__idx on cards  (cost=0.56..946.23 rows=1 width=32) (actual time=0.017..0.017 rows=0 loops=1)
         Index Cond: (name = 'test'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{from,id}'::text[]) = '1234'::text) AND ((data #>> '{to,id}'::text[]) = '5678'::text) AND (jsonb_typeof((data #> '{from}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{to}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{from,id}'::text[])) = 'string'::text) AND (jsonb_typeof((data #> '{to,id}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 3.722 ms
 Execution Time: 0.042 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=0.56..2.86 rows=1 width=32) (actual time=0.019..0.019 rows=0 loops=1)
   ->  Index Scan using t_link__data_from_id__name__data_to_id__idx on cards  (cost=0.56..2.86 rows=1 width=32) (actual time=0.018..0.019 rows=0 loops=1)
         Index Cond: (((data #>> '{from,id}'::text[]) = '1234'::text) AND (name = 'test'::text) AND ((data #>> '{to,id}'::text[]) = '5678'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{from}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{to}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{from,id}'::text[])) = 'string'::text) AND (jsonb_typeof((data #> '{to,id}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.809 ms
 Execution Time: 0.044 ms
(9 rows)

2. summary__data_readBy__idx

Current definition

CREATE INDEX "summary__data_readBy__idx" ON public.cards USING btree (((data ->> 'readBy'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "summary__data_readBy__idx" ON public.cards USING gin (((data #> '{readBy}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'readBy' ],
      properties: {
        readBy: {
          type: 'array',
          contains: {
            const: 'test'
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"readBy"}') = 'array' AND cards.data#>'{"readBy"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.12..968.06 rows=1 width=32) (actual time=0.265..0.266 rows=0 loops=1)
   ->  Index Scan using "summary__data_payload_alertsGroup__idx" on cards  (cost=0.12..968.06 rows=1 width=32) (actual time=0.265..0.265 rows=0 loops=1)
         Filter: (((data #> '{readBy}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{readBy}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 197
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.330 ms
 Execution Time: 0.286 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=1.10..11.48 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=1.10..11.48 rows=1 width=32) (actual time=0.006..0.007 rows=0 loops=1)
         Recheck Cond: (((data #> '{readBy}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{readBy}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_summary__data_readBy__idx"  (cost=0.00..1.10 rows=9 width=0) (actual time=0.006..0.006 rows=0 loops=1)
               Index Cond: ((data #> '{readBy}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.407 ms
 Execution Time: 0.033 ms
(11 rows)

3. summary__data_payload_mentionsUser__idx

Current definition

CREATE INDEX "summary__data_payload_mentionsUser__idx" ON public.cards USING btree ((((data -> 
'payload'::text) ->> 'mentionsUser'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "summary__data_payload_mentionsUser__idx" ON public.cards USING gin (((data #> '{payload,mentionsUser}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'mentionsUser' ],
          properties: {
            mentionsUser: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "mentionsUser"}') = 'array' AND cards.data#>'{"payload", "mentionsUser"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.12..1022.32 rows=1 width=32) (actual time=0.315..0.316 rows=0 loops=1)
   ->  Index Scan using "summary__data_payload_alertsGroup__idx" on cards  (cost=0.12..1022.32 rows=1 width=32) (actual time=0.315..0.315 rows=0 loops=1)
         Filter: (((data #> '{payload,mentionsUser}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 197
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.197 ms
 Execution Time: 0.339 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=1.10..11.55 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=1.10..11.55 rows=1 width=32) (actual time=0.007..0.008 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,mentionsUser}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_summary__data_payload_mentionsUser__idx"  (cost=0.00..1.10 rows=9 width=0) (actual time=0.006..0.006 rows=0 loops=1)
               Index Cond: ((data #> '{payload,mentionsUser}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.410 ms
 Execution Time: 0.035 ms
(11 rows)

4. summary__data_payload_alertsGroup__idx

Current definition

CREATE INDEX "summary__data_payload_alertsGroup__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'alertsGroup'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "summary__data_payload_alertsGroup__idx" ON public.cards USING gin (((data #> '{payload,alertsGroup}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'alertsGroup' ],
          properties: {
            alertsGroup: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "alertsGroup"}') = 'array' AND cards.data#>'{"payload", "alertsGroup"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.12..1022.32 rows=1 width=32) (actual time=0.297..0.297 rows=0 loops=1)
   ->  Index Scan using "summary__data_payload_alertsGroup__idx" on cards  (cost=0.12..1022.32 rows=1 width=32) (actual time=0.296..0.297 rows=0 loops=1)
         Filter: (((data #> '{payload,alertsGroup}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 197
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.218 ms
 Execution Time: 0.320 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=1.10..11.55 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=1.10..11.55 rows=1 width=32) (actual time=0.006..0.007 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,alertsGroup}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_summary__data_payload_alertsGroup__idx"  (cost=0.00..1.10 rows=9 width=0) (actual time=0.005..0.006 rows=0 loops=1)
               Index Cond: ((data #> '{payload,alertsGroup}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.472 ms
 Execution Time: 0.036 ms
(11 rows)

5. summary__data_payload_mentionsGroup__idx

Current definition

CREATE INDEX "summary__data_payload_mentionsGroup__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'mentionsGroup'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "summary__data_payload_mentionsGroup__idx" ON public.cards USING gin (((data #> '{payload,mentionsGroup}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'mentionsGroup' ],
          properties: {
            mentionsGroup: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "mentionsGroup"}') = 'array' AND cards.data#>'{"payload", "mentionsGroup"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.12..1022.32 rows=1 width=32) (actual time=0.300..0.300 rows=0 loops=1)
   ->  Index Scan using "summary__data_payload_alertsGroup__idx" on cards  (cost=0.12..1022.32 rows=1 width=32) (actual time=0.299..0.300 rows=0 loops=1)
         Filter: (((data #> '{payload,mentionsGroup}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 197
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.242 ms
 Execution Time: 0.323 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=1.10..11.55 rows=1 width=32) (actual time=0.007..0.008 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=1.10..11.55 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,mentionsGroup}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_summary__data_payload_mentionsGroup__idx"  (cost=0.00..1.10 rows=9 width=0) (actual time=0.006..0.006 rows=0 loops=1)
               Index Cond: ((data #> '{payload,mentionsGroup}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.447 ms
 Execution Time: 0.033 ms
(11 rows)

6. summary__data_payload_alertsUser__idx

Current definition

CREATE INDEX "summary__data_payload_alertsUser__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'alertsUser'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "summary__data_payload_alertsUser__idx" ON public.cards USING gin (((data #> '{payload,alertsUser}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'alertsUser' ],
          properties: {
            alertsUser: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "alertsUser"}') = 'array' AND cards.data#>'{"payload", "alertsUser"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.12..1022.32 rows=1 width=32) (actual time=0.299..0.299 rows=0 loops=1)
   ->  Index Scan using "summary__data_payload_alertsGroup__idx" on cards  (cost=0.12..1022.32 rows=1 width=32) (actual time=0.298..0.298 rows=0 loops=1)
         Filter: (((data #> '{payload,alertsUser}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 197
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.217 ms
 Execution Time: 0.323 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=1.10..11.55 rows=1 width=32) (actual time=0.006..0.007 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=1.10..11.55 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,alertsUser}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_summary__data_payload_alertsUser__idx"  (cost=0.00..1.10 rows=9 width=0) (actual time=0.005..0.005 rows=0 loops=1)
               Index Cond: ((data #> '{payload,alertsUser}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.441 ms
 Execution Time: 0.034 ms
(11 rows)

7. whisper__data_readBy__idx

Current definition

CREATE INDEX "whisper__data_readBy__idx" ON public.cards USING btree (((data ->> 'readBy'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "whisper__data_readBy__idx" ON public.cards USING gin (((data #> '{readBy}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'readBy' ],
      properties: {
        readBy: {
          type: 'array',
          contains: {
            const: 'test'
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"readBy"}') = 'array' AND cards.data#>'{"readBy"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.42..30776.23 rows=1 width=32) (actual time=180.645..180.646 rows=0 loops=1)
   ->  Index Scan using "whisper__data_payload_alertsGroup__idx" on cards  (cost=0.42..30776.23 rows=1 width=32) (actual time=180.644..180.644 rows=0 loops=1)
         Filter: (((data #> '{readBy}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{readBy}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 210824
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.161 ms
 Execution Time: 180.667 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=4.40..234.56 rows=1 width=32) (actual time=0.013..0.013 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=4.40..234.56 rows=1 width=32) (actual time=0.012..0.013 rows=0 loops=1)
         Recheck Cond: (((data #> '{readBy}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{readBy}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_whisper__data_readBy__idx"  (cost=0.00..4.40 rows=200 width=0) (actual time=0.011..0.012 rows=0 loops=1)
               Index Cond: ((data #> '{readBy}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.368 ms
 Execution Time: 0.040 ms
(11 rows)

8. whisper__data_payload_mentionsUser__idx

Current definition

CREATE INDEX "whisper__data_payload_mentionsUser__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'mentionsUser'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "whisper__data_payload_mentionsUser__idx" ON public.cards USING gin (((data #> '{payload,mentionsUser}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'mentionsUser' ],
          properties: {
            mentionsUser: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "mentionsUser"}') = 'array' AND cards.data#>'{"payload", "mentionsUser"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.42..32404.21 rows=1 width=32) (actual time=227.183..227.185 rows=0 loops=1)
   ->  Index Scan using "whisper__data_payload_alertsGroup__idx" on cards  (cost=0.42..32404.21 rows=1 width=32) (actual time=227.183..227.184 rows=0 loops=1)
         Filter: (((data #> '{payload,mentionsUser}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 210824
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.209 ms
 Execution Time: 227.210 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=4.40..236.06 rows=1 width=32) (actual time=0.015..0.015 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=4.40..236.06 rows=1 width=32) (actual time=0.014..0.014 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,mentionsUser}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_whisper__data_payload_mentionsUser__idx"  (cost=0.00..4.40 rows=200 width=0) (actual time=0.013..0.013 rows=0 loops=1)
               Index Cond: ((data #> '{payload,mentionsUser}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.398 ms
 Execution Time: 0.042 ms
(11 rows)

9. whisper__data_payload_alertsUser__idx

Current definition

CREATE INDEX "whisper__data_payload_alertsUser__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'alertsUser'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "whisper__data_payload_alertsUser__idx" ON public.cards USING gin (((data #> '{payload,alertsUser}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'alertsUser' ],
          properties: {
            alertsUser: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "alertsUser"}') = 'array' AND cards.data#>'{"payload", "alertsUser"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.42..32404.26 rows=1 width=32) (actual time=232.712..232.713 rows=0 loops=1)
   ->  Index Scan using "whisper__data_payload_alertsGroup__idx" on cards  (cost=0.42..32404.26 rows=1 width=32) (actual time=232.711..232.712 rows=0 loops=1)
         Filter: (((data #> '{payload,alertsUser}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 210824
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.261 ms
 Execution Time: 232.738 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=13.20..244.86 rows=1 width=32) (actual time=0.013..0.014 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=13.20..244.86 rows=1 width=32) (actual time=0.013..0.013 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,alertsUser}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_whisper__data_payload_alertsUser__idx"  (cost=0.00..13.20 rows=200 width=0) (actual time=0.011..0.011 rows=0 loops=1)
               Index Cond: ((data #> '{payload,alertsUser}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.447 ms
 Execution Time: 0.043 ms
(11 rows)

10. whisper__data_payload_mentionsGroup__idx

Current definition

CREATE INDEX "whisper__data_payload_mentionsGroup__idx" ON public.cards USING btree ((((data -> 
'payload'::text) ->> 'mentionsGroup'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "whisper__data_payload_mentionsGroup__idx" ON public.cards USING gin (((data #> '{payload,mentionsGroup}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'mentionsGroup' ],
          properties: {
            mentionsGroup: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "mentionsGroup"}') = 'array' AND cards.data#>'{"payload", "mentionsGroup"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.42..32404.26 rows=1 width=32) (actual time=209.834..209.835 rows=0 loops=1)
   ->  Index Scan using "whisper__data_payload_alertsGroup__idx" on cards  (cost=0.42..32404.26 rows=1 width=32) (actual time=209.833..209.834 rows=0 loops=1)
         Filter: (((data #> '{payload,mentionsGroup}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 210826
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.278 ms
 Execution Time: 209.860 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=4.40..236.06 rows=1 width=32) (actual time=0.013..0.014 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=4.40..236.06 rows=1 width=32) (actual time=0.013..0.013 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,mentionsGroup}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_whisper__data_payload_mentionsGroup__idx"  (cost=0.00..4.40 rows=200 width=0) (actual time=0.012..0.012 rows=0 loops=1)
               Index Cond: ((data #> '{payload,mentionsGroup}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.426 ms
 Execution Time: 0.041 ms
(11 rows)

11. whisper__data_payload_alertsGroup__idx

Current definition

CREATE INDEX "whisper__data_payload_alertsGroup__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'alertsGroup'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "whisper__data_payload_alertsGroup__idx" ON public.cards USING gin (((data #> '{payload,alertsGroup}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'alertsGroup' ],
          properties: {
            alertsGroup: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "alertsGroup"}') = 'array' AND cards.data#>'{"payload", "alertsGroup"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.42..32404.26 rows=1 width=32) (actual time=209.493..209.494 rows=0 loops=1)
   ->  Index Scan using "whisper__data_payload_alertsGroup__idx" on cards  (cost=0.42..32404.26 rows=1 width=32) (actual time=209.492..209.493 rows=0 loops=1)
         Filter: (((data #> '{payload,alertsGroup}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 210826
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.214 ms
 Execution Time: 209.520 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=23.10..254.76 rows=1 width=32) (actual time=0.011..0.012 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=23.10..254.76 rows=1 width=32) (actual time=0.011..0.011 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,alertsGroup}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_whisper__data_payload_alertsGroup__idx"  (cost=0.00..23.10 rows=200 width=0) (actual time=0.010..0.010 rows=0 loops=1)
               Index Cond: ((data #> '{payload,alertsGroup}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.457 ms
 Execution Time: 0.040 ms
(11 rows)

12. chart-configuration__data_chartingLibrary__idx

Current definition

CREATE INDEX "chart-configuration__data_chartingLibrary__idx" ON public.cards USING btree (((data ->> 'chartingLibrary'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "chart-configuration__data_chartingLibrary__idx" ON public.cards USING btree (((data #>> '{chartingLibrary}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'chartingLibrary' ],
      properties: {
        chartingLibrary: {
          type: 'string',
          const: 'test'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"chartingLibrary"}') = 'string' AND (cards.data#>>'{"chartingLibrary"}')::text = 'test') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.57..8078.41 rows=1 width=32) (actual time=0.062..0.063 rows=0 loops=1)
   ->  Index Scan using type_cards_idx on cards  (cost=0.57..8078.41 rows=1 width=32) (actual time=0.062..0.062 rows=0 loops=1)
         Index Cond: (type = '[email protected]'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{chartingLibrary}'::text[]) = 'test'::text) AND (jsonb_typeof((data #> '{chartingLibrary}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 1
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.143 ms
 Execution Time: 0.084 ms
(10 rows)

After adding new index with proposed definition:

 Limit  (cost=0.12..50.43 rows=1 width=32) (actual time=0.005..0.006 rows=0 loops=1)
   ->  Index Scan using "t_chart-configuration__data_chartingLibrary__idx" on cards  (cost=0.12..50.43 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=1)
         Index Cond: ((data #>> '{chartingLibrary}'::text[]) = 'test'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{chartingLibrary}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.390 ms
 Execution Time: 0.026 ms
(9 rows)

13. rating__data_readBy__idx

Current definition

CREATE INDEX "rating__data_readBy__idx" ON public.cards USING btree (((data ->> 'readBy'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "rating__data_readBy__idx" ON public.cards USING gin (((data #> '{readBy}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'readBy' ],
      properties: {
        readBy: {
          type: 'array',
          contains: {
            const: 'test'
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"readBy"}') = 'array' AND cards.data#>'{"readBy"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.57..8078.41 rows=1 width=32) (actual time=1.220..1.221 rows=0 loops=1)
   ->  Index Scan using type_cards_idx on cards  (cost=0.57..8078.41 rows=1 width=32) (actual time=1.220..1.220 rows=0 loops=1)
         Index Cond: (type = '[email protected]'::text)
         Filter: (((data #> '{readBy}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{readBy}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 4
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.149 ms
 Execution Time: 1.244 ms
(10 rows)

After adding new index with proposed definition:

 Limit  (cost=1.10..11.48 rows=1 width=32) (actual time=0.007..0.008 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=1.10..11.48 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=1)
         Recheck Cond: (((data #> '{readBy}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{readBy}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_rating__data_readBy__idx"  (cost=0.00..1.10 rows=9 width=0) (actual time=0.005..0.005 rows=0 loops=1)
               Index Cond: ((data #> '{readBy}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.337 ms
 Execution Time: 0.035 ms
(11 rows)

14. rating__data_payload_mentionsUser__idx

Current definition

CREATE INDEX "rating__data_payload_mentionsUser__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'mentionsUser'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "rating__data_payload_mentionsUser__idx" ON public.cards USING gin (((data #> '{payload,mentionsUser}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'mentionsUser' ],
          properties: {
            mentionsUser: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "mentionsUser"}') = 'array' AND cards.data#>'{"payload", "mentionsUser"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.57..8132.67 rows=1 width=32) (actual time=0.029..0.030 rows=0 loops=1)
   ->  Index Scan using type_cards_idx on cards  (cost=0.57..8132.67 rows=1 width=32) (actual time=0.029..0.029 rows=0 loops=1)
         Index Cond: (type = '[email protected]'::text)
         Filter: (((data #> '{payload,mentionsUser}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 4
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.198 ms
 Execution Time: 0.052 ms
(10 rows)

After adding new index with proposed definition:

 Limit  (cost=1.10..11.55 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=1.10..11.55 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,mentionsUser}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_rating__data_payload_mentionsUser__idx"  (cost=0.00..1.10 rows=9 width=0) (actual time=0.005..0.005 rows=0 loops=1)
               Index Cond: ((data #> '{payload,mentionsUser}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.467 ms
 Execution Time: 0.040 ms
(11 rows)

15. rating__data_payload_alertsUser__idx

Current definition

CREATE INDEX "rating__data_payload_alertsUser__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'alertsUser'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "rating__data_payload_alertsUser__idx" ON public.cards USING gin (((data #> '{payload,alertsUser}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'alertsUser' ],
          properties: {
            alertsUser: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "alertsUser"}') = 'array' AND cards.data#>'{"payload", "alertsUser"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.57..8132.67 rows=1 width=32) (actual time=0.030..0.031 rows=0 loops=1)
   ->  Index Scan using type_cards_idx on cards  (cost=0.57..8132.67 rows=1 width=32) (actual time=0.030..0.030 rows=0 loops=1)
         Index Cond: (type = '[email protected]'::text)
         Filter: (((data #> '{payload,alertsUser}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 4
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.219 ms
 Execution Time: 0.055 ms
(10 rows)

After adding new index with proposed definition:

 Limit  (cost=1.10..11.55 rows=1 width=32) (actual time=0.007..0.008 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=1.10..11.55 rows=1 width=32) (actual time=0.006..0.007 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,alertsUser}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_rating__data_payload_alertsUser__idx"  (cost=0.00..1.10 rows=9 width=0) (actual time=0.005..0.005 rows=0 loops=1)
               Index Cond: ((data #> '{payload,alertsUser}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.463 ms
 Execution Time: 0.039 ms
(11 rows)

16. rating__data_payload_mentionsGroup__idx

Current definition

CREATE INDEX "rating__data_payload_mentionsGroup__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'mentionsGroup'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "rating__data_payload_mentionsGroup__idx" ON public.cards USING gin (((data #> '{payload,mentionsGroup}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'mentionsGroup' ],
          properties: {
            mentionsGroup: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "mentionsGroup"}') = 'array' AND cards.data#>'{"payload", "mentionsGroup"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.57..8132.67 rows=1 width=32) (actual time=0.031..0.032 rows=0 loops=1)
   ->  Index Scan using type_cards_idx on cards  (cost=0.57..8132.67 rows=1 width=32) (actual time=0.031..0.031 rows=0 loops=1)
         Index Cond: (type = '[email protected]'::text)
         Filter: (((data #> '{payload,mentionsGroup}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 4
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.204 ms
 Execution Time: 0.057 ms
(10 rows)

After adding new index with proposed definition:

 Limit  (cost=1.10..11.55 rows=1 width=32) (actual time=0.007..0.008 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=1.10..11.55 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,mentionsGroup}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_rating__data_payload_mentionsGroup__idx"  (cost=0.00..1.10 rows=9 width=0) (actual time=0.005..0.006 rows=0 loops=1)
               Index Cond: ((data #> '{payload,mentionsGroup}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.497 ms
 Execution Time: 0.038 ms
(11 rows)

17. rating__data_payload_alertsGroup__idx

Current definition

CREATE INDEX "rating__data_payload_alertsGroup__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'alertsGroup'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "rating__data_payload_alertsGroup__idx" ON public.cards USING gin (((data #> '{payload,alertsGroup}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'alertsGroup' ],
          properties: {
            alertsGroup: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "alertsGroup"}') = 'array' AND cards.data#>'{"payload", "alertsGroup"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.57..8132.67 rows=1 width=32) (actual time=0.030..0.031 rows=0 loops=1)
   ->  Index Scan using type_cards_idx on cards  (cost=0.57..8132.67 rows=1 width=32) (actual time=0.030..0.030 rows=0 loops=1)
         Index Cond: (type = '[email protected]'::text)
         Filter: (((data #> '{payload,alertsGroup}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 4
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.200 ms
 Execution Time: 0.053 ms
(10 rows)

After adding new index with proposed definition:

 Limit  (cost=1.10..11.55 rows=1 width=32) (actual time=0.007..0.008 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=1.10..11.55 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,alertsGroup}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_rating__data_payload_alertsGroup__idx"  (cost=0.00..1.10 rows=9 width=0) (actual time=0.005..0.005 rows=0 loops=1)
               Index Cond: ((data #> '{payload,alertsGroup}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.458 ms
 Execution Time: 0.037 ms
(11 rows)

18. message__data_readBy__idx

Current definition

CREATE INDEX "message__data_readBy__idx" ON public.cards USING btree (((data ->> 'readBy'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "message__data_readBy__idx" ON public.cards USING gin (((data #> '{readBy}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'readBy' ],
      properties: {
        readBy: {
          type: 'array',
          contains: {
            const: 'test'
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"readBy"}') = 'array' AND cards.data#>'{"readBy"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.42..44889.29 rows=1 width=32) (actual time=684.832..684.833 rows=0 loops=1)
   ->  Index Scan using "message__data_payload_alertsGroup__idx" on cards  (cost=0.42..44889.29 rows=1 width=32) (actual time=684.831..684.832 rows=0 loops=1)
         Filter: (((data #> '{readBy}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{readBy}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 331364
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.119 ms
 Execution Time: 684.857 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=3.31..355.40 rows=1 width=32) (actual time=0.016..0.016 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=3.31..355.40 rows=1 width=32) (actual time=0.015..0.016 rows=0 loops=1)
         Recheck Cond: (((data #> '{readBy}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{readBy}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_message__data_readBy__idx"  (cost=0.00..3.31 rows=306 width=0) (actual time=0.014..0.014 rows=0 loops=1)
               Index Cond: ((data #> '{readBy}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.324 ms
 Execution Time: 0.043 ms
(11 rows)

19. message__data_payload_mentionsUser__idx

Current definition

CREATE INDEX "message__data_payload_mentionsUser__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'mentionsUser'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "message__data_payload_mentionsUser__idx" ON public.cards USING gin (((data #> '{payload,mentionsUser}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'mentionsUser' ],
          properties: {
            mentionsUser: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "mentionsUser"}') = 'array' AND cards.data#>'{"payload", "mentionsUser"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.42..47258.99 rows=1 width=32) (actual time=762.793..762.794 rows=0 loops=1)
   ->  Index Scan using "message__data_payload_alertsGroup__idx" on cards  (cost=0.42..47258.99 rows=1 width=32) (actual time=762.792..762.793 rows=0 loops=1)
         Filter: (((data #> '{payload,mentionsUser}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 331364
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.192 ms
 Execution Time: 762.817 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=4.41..358.79 rows=1 width=32) (actual time=0.017..0.018 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=4.41..358.79 rows=1 width=32) (actual time=0.017..0.017 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,mentionsUser}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_message__data_payload_mentionsUser__idx"  (cost=0.00..4.41 rows=306 width=0) (actual time=0.015..0.015 rows=0 loops=1)
               Index Cond: ((data #> '{payload,mentionsUser}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.479 ms
 Execution Time: 0.046 ms
(11 rows)

20. message__data_payload_alertsUser__idx

Current definition

CREATE INDEX "message__data_payload_alertsUser__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'alertsUser'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "message__data_payload_alertsUser__idx" ON public.cards USING gin (((data #> '{payload,alertsUser}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'alertsUser' ],
          properties: {
            alertsUser: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "alertsUser"}') = 'array' AND cards.data#>'{"payload", "alertsUser"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.42..47258.99 rows=1 width=32) (actual time=767.707..767.708 rows=0 loops=1)
   ->  Index Scan using "message__data_payload_alertsGroup__idx" on cards  (cost=0.42..47258.99 rows=1 width=32) (actual time=767.706..767.707 rows=0 loops=1)
         Filter: (((data #> '{payload,alertsUser}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 331365
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.169 ms
 Execution Time: 767.731 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=7.71..362.09 rows=1 width=32) (actual time=0.014..0.015 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=7.71..362.09 rows=1 width=32) (actual time=0.014..0.014 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,alertsUser}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsUser}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_message__data_payload_alertsUser__idx"  (cost=0.00..7.71 rows=306 width=0) (actual time=0.013..0.013 rows=0 loops=1)
               Index Cond: ((data #> '{payload,alertsUser}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.441 ms
 Execution Time: 0.042 ms
(11 rows)

21. message__data_payload_alertsGroup__idx

Current definition

CREATE INDEX "message__data_payload_alertsGroup__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'alertsGroup'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "message__data_payload_alertsGroup__idx" ON public.cards USING gin (((data #> '{payload,alertsGroup}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'alertsGroup' ],
          properties: {
            alertsGroup: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "alertsGroup"}') = 'array' AND cards.data#>'{"payload", "alertsGroup"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.42..47258.99 rows=1 width=32) (actual time=746.816..746.817 rows=0 loops=1)
   ->  Index Scan using "message__data_payload_alertsGroup__idx" on cards  (cost=0.42..47258.99 rows=1 width=32) (actual time=746.815..746.816 rows=0 loops=1)
         Filter: (((data #> '{payload,alertsGroup}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 331365
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.163 ms
 Execution Time: 746.843 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=28.61..382.99 rows=1 width=32) (actual time=0.014..0.015 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=28.61..382.99 rows=1 width=32) (actual time=0.014..0.014 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,alertsGroup}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,alertsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_message__data_payload_alertsGroup__idx"  (cost=0.00..28.61 rows=306 width=0) (actual time=0.013..0.013 rows=0 loops=1)
               Index Cond: ((data #> '{payload,alertsGroup}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.444 ms
 Execution Time: 0.043 ms
(11 rows)

22. message__data_payload_mentionsGroup__idx

Current definition

CREATE INDEX "message__data_payload_mentionsGroup__idx" ON public.cards USING btree ((((data -> 'payload'::text) ->> 'mentionsGroup'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "message__data_payload_mentionsGroup__idx" ON public.cards USING gin (((data #> '{payload,mentionsGroup}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'payload' ],
      properties: {
        payload: {
          type: 'object',
          required: [ 'mentionsGroup' ],
          properties: {
            mentionsGroup: {
              type: 'array',
              contains: {
                const: 'test'
              }
            }
          }
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"payload"}') = 'object' AND jsonb_typeof(cards.data#>'{"payload", "mentionsGroup"}') = 'array' AND cards.data#>'{"payload", "mentionsGroup"}' @> '"test"') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.42..47258.99 rows=1 width=32) (actual time=749.887..749.888 rows=0 loops=1)
   ->  Index Scan using "message__data_payload_alertsGroup__idx" on cards  (cost=0.42..47258.99 rows=1 width=32) (actual time=749.885..749.886 rows=0 loops=1)
         Filter: (((data #> '{payload,mentionsGroup}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 331365
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.216 ms
 Execution Time: 749.913 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=9.91..364.29 rows=1 width=32) (actual time=0.015..0.015 rows=0 loops=1)
   ->  Bitmap Heap Scan on cards  (cost=9.91..364.29 rows=1 width=32) (actual time=0.014..0.014 rows=0 loops=1)
         Recheck Cond: (((data #> '{payload,mentionsGroup}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{payload}'::text[])) = 'object'::text) AND (jsonb_typeof((data #> '{payload,mentionsGroup}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_message__data_payload_mentionsGroup__idx"  (cost=0.00..9.91 rows=306 width=0) (actual time=0.012..0.013 rows=0 loops=1)
               Index Cond: ((data #> '{payload,mentionsGroup}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.458 ms
 Execution Time: 0.042 ms
(11 rows)

23. support-thread__data_status__data_category__idx

Current definition

CREATE INDEX "support-thread__data_status__data_category__idx" ON public.cards USING btree (((data ->> 'status'::text)), ((data ->> 'category'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "support-thread__data_status__data_category__idx" ON public.cards USING btree (((data #>> '{status}'::text[])), ((data #>> '{category}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'status', 'category' ],
      properties: {
        status: {
          type: 'string',
          const: 'open'
        },
        category: {
          type: 'string',
          const: 'test'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"status"}') = 'string' AND (cards.data#>>'{"status"}')::text = 'open' AND jsonb_typeof(cards.data#>'{"category"}') = 'string' AND (cards.data#>>'{"category"}')::text = 'test') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.28..1205.99 rows=1 width=32) (actual time=9.764..9.765 rows=0 loops=1)
   ->  Index Scan using "support-thread__data_status__idx" on cards  (cost=0.28..1205.99 rows=1 width=32) (actual time=9.763..9.764 rows=0 loops=1)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{status}'::text[]) = 'open'::text) AND ((data #>> '{category}'::text[]) = 'test'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (jsonb_typeof((data #> '{category}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 12982
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.347 ms
 Execution Time: 9.793 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=0.28..2.56 rows=1 width=32) (actual time=0.012..0.012 rows=0 loops=1)
   ->  Index Scan using "t_support-thread__data_status__data_category__idx" on cards  (cost=0.28..2.56 rows=1 width=32) (actual time=0.011..0.011 rows=0 loops=1)
         Index Cond: (((data #>> '{status}'::text[]) = 'open'::text) AND ((data #>> '{category}'::text[]) = 'test'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (jsonb_typeof((data #> '{category}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.525 ms
 Execution Time: 0.034 ms
(9 rows)

24. web-push-subscription__data_endpoint__idx

Current definition

CREATE INDEX "web-push-subscription__data_endpoint__idx" ON public.cards USING btree (((data ->> 'endpoint'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "web-push-subscription__data_endpoint__idx" ON public.cards USING btree (((data #>> '{endpoint}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'endpoint' ],
      properties: {
        endpoint: {
          type: 'string',
          const: 'test'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"endpoint"}') = 'string' AND (cards.data#>>'{"endpoint"}')::text = 'test') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.57..8079.51 rows=1 width=32) (actual time=0.781..0.781 rows=0 loops=1)
   ->  Index Scan using type_cards_idx on cards  (cost=0.57..8079.51 rows=1 width=32) (actual time=0.780..0.781 rows=0 loops=1)
         Index Cond: (type = '[email protected]'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{endpoint}'::text[]) = 'test'::text) AND (jsonb_typeof((data #> '{endpoint}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.181 ms
 Execution Time: 0.807 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=0.12..50.43 rows=1 width=32) (actual time=0.004..0.005 rows=0 loops=1)
   ->  Index Scan using "t_web-push-subscription__data_endpoint__idx" on cards  (cost=0.12..50.43 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=1)
         Index Cond: ((data #>> '{endpoint}'::text[]) = 'test'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{endpoint}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.344 ms
 Execution Time: 0.029 ms
(9 rows)

25. contract-repository__data_base_slug__idx

Current definition

CREATE INDEX "contract-repository__data_base_slug__idx" ON public.cards USING btree (((data ->> 'base_slug'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "contract-repository__data_base_slug__idx" ON public.cards USING btree (((data #>> '{base_slug}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'base_slug' ],
      properties: {
        base_slug: {
          type: 'string',
          const: 'test'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"base_slug"}') = 'string' AND (cards.data#>>'{"base_slug"}')::text = 'test') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.57..8079.51 rows=1 width=32) (actual time=0.046..0.046 rows=0 loops=1)
   ->  Index Scan using type_cards_idx on cards  (cost=0.57..8079.51 rows=1 width=32) (actual time=0.045..0.046 rows=0 loops=1)
         Index Cond: (type = '[email protected]'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{base_slug}'::text[]) = 'test'::text) AND (jsonb_typeof((data #> '{base_slug}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 19
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.169 ms
 Execution Time: 0.068 ms
(10 rows)

After adding new index with proposed definition:

 Limit  (cost=0.12..50.43 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=1)
   ->  Index Scan using "t_contract-repository__data_base_slug__idx" on cards  (cost=0.12..50.43 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=1)
         Index Cond: ((data #>> '{base_slug}'::text[]) = 'test'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{base_slug}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.312 ms
 Execution Time: 0.025 ms
(9 rows)

26. contract-repository__data_base_type__idx

Current definition

CREATE INDEX "contract-repository__data_base_type__idx" ON public.cards USING btree (((data ->> 'base_type'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "contract-repository__data_base_type__idx" ON public.cards USING btree (((data #>> '{base_type}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'base_type' ],
      properties: {
        base_type: {
          type: 'string',
          const: 'test'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"base_type"}') = 'string' AND (cards.data#>>'{"base_type"}')::text = 'test') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.57..8079.51 rows=1 width=32) (actual time=0.049..0.049 rows=0 loops=1)
   ->  Index Scan using type_cards_idx on cards  (cost=0.57..8079.51 rows=1 width=32) (actual time=0.048..0.048 rows=0 loops=1)
         Index Cond: (type = '[email protected]'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{base_type}'::text[]) = 'test'::text) AND (jsonb_typeof((data #> '{base_type}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 19
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.161 ms
 Execution Time: 0.074 ms
(10 rows)

After adding new index with proposed definition:

 Limit  (cost=0.12..50.43 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=1)
   ->  Index Scan using "t_contract-repository__data_base_type__idx" on cards  (cost=0.12..50.43 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=1)
         Index Cond: ((data #>> '{base_type}'::text[]) = 'test'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{base_type}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.308 ms
 Execution Time: 0.028 ms
(9 rows)

27. support-thread__data_status__idx

Current definition

CREATE INDEX "support-thread__data_status__idx" ON public.cards USING btree (((data ->> 'status'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "support-thread__data_status__idx" ON public.cards USING btree (((data #>> '{status}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'status' ],
      properties: {
        status: {
          type: 'string',
          const: 'open'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"status"}') = 'string' AND (cards.data#>>'{"status"}')::text = 'open') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.28..1115.54 rows=1 width=32) (actual time=9.150..19.405 rows=512 loops=1)
   ->  Index Scan using "support-thread__data_status__idx" on cards  (cost=0.28..1115.54 rows=1 width=32) (actual time=9.150..19.362 rows=512 loops=1)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{status}'::text[]) = 'open'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 12470
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.003..0.003 rows=0 loops=512)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                 Rows Removed by Filter: 0
 Planning Time: 1.103 ms
 Execution Time: 19.449 ms
(10 rows)

After adding new index with proposed definition:

 Limit  (cost=0.28..40.11 rows=1 width=32) (actual time=0.085..10.314 rows=511 loops=1)
   ->  Index Scan using "t_support-thread__data_status__idx" on cards  (cost=0.28..40.11 rows=1 width=32) (actual time=0.084..10.271 rows=511 loops=1)
         Index Cond: ((data #>> '{status}'::text[]) = 'open'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.003..0.003 rows=0 loops=511)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                 Rows Removed by Filter: 0
 Planning Time: 1.398 ms
 Execution Time: 10.362 ms
(10 rows)

28. sales-thread__data_status__idx

Current definition

CREATE INDEX "sales-thread__data_status__idx" ON public.cards USING btree (((data ->> 'status'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "sales-thread__data_status__idx" ON public.cards USING btree (((data #>> '{status}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'status' ],
      properties: {
        status: {
          type: 'string',
          const: 'open'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"status"}') = 'string' AND (cards.data#>>'{"status"}')::text = 'open') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.28..1042.94 rows=1 width=32) (actual time=0.340..17.489 rows=1000 loops=1)
   ->  Index Scan using "sales-thread__data_status__idx" on cards  (cost=0.28..1042.94 rows=1 width=32) (actual time=0.339..17.407 rows=1000 loops=1)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{status}'::text[]) = 'open'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 231
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=1000)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.195 ms
 Execution Time: 17.555 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=0.25..50.56 rows=1 width=32) (actual time=0.064..17.100 rows=1000 loops=1)
   ->  Index Scan using "t_sales-thread__data_status__idx" on cards  (cost=0.25..50.56 rows=1 width=32) (actual time=0.064..17.016 rows=1000 loops=1)
         Index Cond: ((data #>> '{status}'::text[]) = 'open'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=1000)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.391 ms
 Execution Time: 17.167 ms
(9 rows)

29. pull-request__data_status__idx

Current definition

CREATE INDEX "pull-request__data_status__idx" ON public.cards USING btree (((data ->> 'status'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "pull-request__data_status__idx" ON public.cards USING btree (((data #>> '{status}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'status' ],
      properties: {
        status: {
          type: 'string',
          const: 'open'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"status"}') = 'string' AND (cards.data#>>'{"status"}')::text = 'open') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.29..6808.97 rows=1 width=32) (actual time=217.173..240.811 rows=1000 loops=1)
   ->  Index Scan using "pull-request__data_status__idx" on cards  (cost=0.29..6808.97 rows=1 width=32) (actual time=217.172..240.709 rows=1000 loops=1)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{status}'::text[]) = 'open'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 38454
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=1000)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.105 ms
 Execution Time: 240.879 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=0.29..25.17 rows=1 width=32) (actual time=0.079..22.433 rows=1000 loops=1)
   ->  Index Scan using "t_pull-request__data_status__idx" on cards  (cost=0.29..25.17 rows=1 width=32) (actual time=0.078..22.346 rows=1000 loops=1)
         Index Cond: ((data #>> '{status}'::text[]) = 'open'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=1000)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.396 ms
 Execution Time: 22.503 ms
(9 rows)

30. brainstorm-topic__data_status__idx

Current definition

CREATE INDEX "brainstorm-topic__data_status__idx" ON public.cards USING btree (((data ->> 'status'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "brainstorm-topic__data_status__idx" ON public.cards USING btree (((data #>> '{status}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'status' ],
      properties: {
        status: {
          type: 'string',
          const: 'open'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"status"}') = 'string' AND (cards.data#>>'{"status"}')::text = 'open') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.25..968.23 rows=1 width=32) (actual time=1.023..3.420 rows=128 loops=1)
   ->  Index Scan using "brainstorm-topic__data_status__idx" on cards  (cost=0.25..968.23 rows=1 width=32) (actual time=1.022..3.408 rows=128 loops=1)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{status}'::text[]) = 'open'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 558
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=130)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                 Rows Removed by Filter: 0
 Planning Time: 1.198 ms
 Execution Time: 3.446 ms
(10 rows)

After adding new index with proposed definition:

 Limit  (cost=0.25..50.56 rows=1 width=32) (actual time=0.065..2.410 rows=127 loops=1)
   ->  Index Scan using "t_brainstorm-topic__data_status__idx" on cards  (cost=0.25..50.56 rows=1 width=32) (actual time=0.065..2.398 rows=127 loops=1)
         Index Cond: ((data #>> '{status}'::text[]) = 'open'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 2
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=129)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                 Rows Removed by Filter: 0
 Planning Time: 1.397 ms
 Execution Time: 2.437 ms
(11 rows)

31. user-feedback__data_status__idx

Current definition

CREATE INDEX "user-feedback__data_status__idx" ON public.cards USING btree (((data ->> 'status'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "user-feedback__data_status__idx" ON public.cards USING btree (((data #>> '{status}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'status' ],
      properties: {
        status: {
          type: 'string',
          const: 'open'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"status"}') = 'string' AND (cards.data#>>'{"status"}')::text = 'open') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.25..968.23 rows=1 width=32) (actual time=14.447..17.085 rows=64 loops=1)
   ->  Index Scan using "user-feedback__data_status__idx" on cards  (cost=0.25..968.23 rows=1 width=32) (actual time=14.446..17.079 rows=64 loops=1)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{status}'::text[]) = 'open'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 3130
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=64)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.154 ms
 Execution Time: 17.110 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=0.25..50.56 rows=1 width=32) (actual time=0.088..1.401 rows=64 loops=1)
   ->  Index Scan using "t_user-feedback__data_status__idx" on cards  (cost=0.25..50.56 rows=1 width=32) (actual time=0.087..1.394 rows=64 loops=1)
         Index Cond: ((data #>> '{status}'::text[]) = 'open'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=64)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.437 ms
 Execution Time: 1.426 ms
(9 rows)

32. check-run__data_status__idx

Current definition

CREATE INDEX "check-run__data_status__idx" ON public.cards USING btree (((data ->> 'status'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX "check-run__data_status__idx" ON public.cards USING btree (((data #>> '{status}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'status' ],
      properties: {
        status: {
          type: 'string',
          const: 'open'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"status"}') = 'string' AND (cards.data#>>'{"status"}')::text = 'open') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.29..2454.25 rows=1 width=32) (actual time=12.226..12.227 rows=0 loops=1)
   ->  Index Scan using "check-run__data_status__idx" on cards  (cost=0.29..2454.25 rows=1 width=32) (actual time=12.225..12.226 rows=0 loops=1)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{status}'::text[]) = 'open'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 9917
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.159 ms
 Execution Time: 12.250 ms
(9 rows)

After adding new index with proposed definition:

 Limit  (cost=0.29..7.71 rows=1 width=32) (actual time=0.009..0.010 rows=0 loops=1)
   ->  Index Scan using "t_check-run__data_status__idx" on cards  (cost=0.29..7.71 rows=1 width=32) (actual time=0.009..0.009 rows=0 loops=1)
         Index Cond: ((data #>> '{status}'::text[]) = 'open'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.393 ms
 Execution Time: 0.030 ms
(9 rows)

33. pattern__data_status__idx

Current definition

CREATE INDEX pattern__data_status__idx ON public.cards USING btree (((data ->> 'status'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX pattern__data_status__idx ON public.cards USING btree (((data #>> '{status}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'status' ],
      properties: {
        status: {
          type: 'string',
          const: 'open'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"status"}') = 'string' AND (cards.data#>>'{"status"}')::text = 'open') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.57..8079.51 rows=1 width=32) (actual time=0.075..3.409 rows=172 loops=1)
   ->  Index Scan using type_cards_idx on cards  (cost=0.57..8079.51 rows=1 width=32) (actual time=0.075..3.394 rows=172 loops=1)
         Index Cond: (type = '[email protected]'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{status}'::text[]) = 'open'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 190
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=172)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                 Rows Removed by Filter: 1
 Planning Time: 1.152 ms
 Execution Time: 3.438 ms
(11 rows)

After adding new index with proposed definition:

 Limit  (cost=0.12..50.43 rows=1 width=32) (actual time=0.064..3.082 rows=171 loops=1)
   ->  Index Scan using t_pattern__data_status__idx on cards  (cost=0.12..50.43 rows=1 width=32) (actual time=0.064..3.067 rows=171 loops=1)
         Index Cond: ((data #>> '{status}'::text[]) = 'open'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=171)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                 Rows Removed by Filter: 1
 Planning Time: 1.413 ms
 Execution Time: 3.110 ms
(10 rows)

34. improvement__data_status__idx

Current definition

CREATE INDEX improvement__data_status__idx ON public.cards USING btree (((data ->> 'status'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX improvement__data_status__idx ON public.cards USING btree (((data #>> '{status}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'status' ],
      properties: {
        status: {
          type: 'string',
          const: 'open'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"status"}') = 'string' AND (cards.data#>>'{"status"}')::text = 'open') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.57..8079.51 rows=1 width=32) (actual time=1.584..1.585 rows=0 loops=1)
   ->  Index Scan using type_cards_idx on cards  (cost=0.57..8079.51 rows=1 width=32) (actual time=1.584..1.584 rows=0 loops=1)
         Index Cond: (type = '[email protected]'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{status}'::text[]) = 'open'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 131
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.150 ms
 Execution Time: 1.606 ms
(10 rows)

After adding new index with proposed definition:

 Limit  (cost=0.12..50.43 rows=1 width=32) (actual time=0.006..0.007 rows=0 loops=1)
   ->  Index Scan using t_improvement__data_status__idx on cards  (cost=0.12..50.43 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=1)
         Index Cond: ((data #>> '{status}'::text[]) = 'open'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.371 ms
 Execution Time: 0.026 ms
(9 rows)

35. milestone__data_status__idx

Current definition

CREATE INDEX milestone__data_status__idx ON public.cards USING btree (((data ->> 'status'::text))) WHERE (type = '[email protected]'::text)

Proposed definition

CREATE INDEX milestone__data_status__idx ON public.cards USING btree (((data #>> '{status}'::text[]))) WHERE (type = '[email protected]'::text)

Query

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'status' ],
      properties: {
        status: {
          type: 'string',
          const: 'open'
        }
      }
    }
  }
})

Compiled SQL

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"status"}') = 'string' AND (cards.data#>>'{"status"}')::text = 'open') LIMIT 1000;

`EXPLAIN ANALYZE SELECT` results on production

With current index only:

 Limit  (cost=0.57..8079.51 rows=1 width=32) (actual time=0.059..0.321 rows=14 loops=1)
   ->  Index Scan using type_cards_idx on cards  (cost=0.57..8079.51 rows=1 width=32) (actual time=0.059..0.319 rows=14 loops=1)
         Index Cond: (type = '[email protected]'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND ((data #>> '{status}'::text[]) = 'open'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         Rows Removed by Filter: 5
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=14)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                 Rows Removed by Filter: 1
 Planning Time: 1.113 ms
 Execution Time: 0.341 ms
(11 rows)

After adding new index with proposed definition:

 Limit  (cost=0.12..50.43 rows=1 width=32) (actual time=0.060..0.333 rows=14 loops=1)
   ->  Index Scan using t_milestone__data_status__idx on cards  (cost=0.12..50.43 rows=1 width=32) (actual time=0.060..0.331 rows=14 loops=1)
         Index Cond: ((data #>> '{status}'::text[]) = 'open'::text)
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{status}'::text[])) = 'string'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=14)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                 Rows Removed by Filter: 1
 Planning Time: 1.385 ms
 Execution Time: 0.355 ms
(10 rows)

CVE-2021-23382 (Medium) detected in postcss-7.0.35.tgz - autoclosed

CVE-2021-23382 - Medium Severity Vulnerability

Vulnerable Library - postcss-7.0.35.tgz

Tool for transforming styles with JS plugins

Library home page: https://registry.npmjs.org/postcss/-/postcss-7.0.35.tgz

Dependency Hierarchy:

depcheck-1.4.0.tgz (Root Library)
- compiler-sfc-3.0.5.tgz
  - ❌ postcss-7.0.35.tgz (Vulnerable Library)

Found in HEAD commit: d88fa280faa5d22992032c6f000f9257017af553

Found in base branch: master

Vulnerability Details

The package postcss before 8.2.13 are vulnerable to Regular Expression Denial of Service (ReDoS) via getAnnotationURL() and loadAnnotation() in lib/previous-map.js. The vulnerable regexes are caused mainly by the sub-pattern /*\s* sourceMappingURL=(.*).

Publish Date: 2021-04-26

URL: CVE-2021-23382

CVSS 3 Score Details (5.3)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Network
- Attack Complexity: Low
- Privileges Required: None
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: None
- Integrity Impact: None
- Availability Impact: Low

For more information on CVSS3 Scores, click here.

Suggested Fix

Type: Upgrade version

Origin: https://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2021-23382

Release Date: 2021-04-26

Fix Resolution: postcss - 8.2.13

Step up your Open Source Security Game with WhiteSource here

Don't export the `Backend` object

This is an implementation detail that should not be exported.

"missing FROM-clause entry for table" with link query

Getting this error

HTTP unexpected error {"name":"error","message":"missing FROM-clause entry for table \"linked@/has attached check run\"","stack":"error: missing FROM-clause entry for table \"linked@/has attached check run\"\n at Parser.parseErrorMessage (/usr/src/jellyfish/apps/server/node_modules/pg-protocol/dist/parser.js:287:98)\n at Parser.handlePacket (/usr/src/jellyfish/apps/server/node_modules/pg-protocol/dist/parser.js:126:29)\n at Parser.parse (/usr/src/jellyfish/apps/server/node_modules/pg-protocol/dist/parser.js:39:38)\n at Socket.<anonymous> (/usr/src/jellyfish/apps/server/node_modules/pg-protocol/dist/index.js:11:42)\n at Socket.emit (events.js:376:20)\n at Socket.emit (domain.js:470:12)\n at addChunk (internal/streams/readable.js:309:12)\n at readableAddChunk (internal/streams/readable.js:284:9)\n at Socket.Readable.push (internal/streams/readable.js:223:10)\n at TCP.onStreamRead (internal/stream_base_commons.js:188:23)\n at TCP.callbackTrampoline (internal/async_hooks.js:134:14)","length":144,"severity":"ERROR","code":"42P01","position":"40559","file":"parse_relation.c","line":"3305","routine":"errorMissingRTE"}

when running this query

{
  type: 'object',
  properties: {
    type: {
      const: '[email protected]',
    },
  },
  anyOf: [
    true,
    {
      $$links: {
        'was built into': true,
        'has attached commit': {
          type: 'object',
          anyOf: [
            true,
            {
              $$links: {
                'was built into': true,
                'has attached check run': true,
              },
            },
          ],
        },
      },
    },
  ],
};

on JF version
v36.0.0 cephea

Constant folding optimizes out `$$links`

In:

{
    type: "object",
    properties: {
        type: {
            const: "[email protected]",
        },
    },
    anyOf: [
        {
            $$links: {
                "is attached to": {
                    properties: {
                        type: {
                            const: "[email protected]",
                        },
                    },
                    additionalProperties: true,
                },
            },
        },
        true,
    ],
    additionalProperties: true,
}

The link is optimized out due to the true branch in the anyOf and the link is not fetched but still referenced in the select list, leading to an SQL error.

error "Cannot use 'in' operator to search for '$$links'" although I don't use links

This simple query returned that error:

{
    "query": {
        "type": "object",
        "properties": {
            "type": {
                "const": "[email protected]"
            },
            "data": {
                "type": "object",
                "properties": {
                    "input": {
                        "type": "object",
                        "properties": {
                            "id": "a7fa2819-9c71-4e9e-ba4e-c17296536cda"
                        }
                    }
                }
            }
        }
    },
    "options": {}
}

api_1                    | 2021-10-22T10:11:59.283Z [build/http/routes] [warning] [REQUEST-39.1.55-3e76213b-20fe-4180-b9b5-4e6d52157d24]: JSON Schema query error {"query":{"type":"object","properties":{"type":{"const":"[email protected]"},"data":{"type":"object","properties":{"input":{"type":"object","properties":{"id":"a7fa2819-9c71-4e9e-ba4e-c17296536cda"}}}}}},"options":{}}
api_1                    | 2021-10-22T10:11:59.284Z [build/http/routes] [crit] [REQUEST-39.1.55-3e76213b-20fe-4180-b9b5-4e6d52157d24]: HTTP unexpected error {"name":"TypeError","message":"Cannot use 'in' operator to search for '$$links' in a7fa2819-9c71-4e9e-ba4e-c17296536cda","stack":"TypeError: Cannot use 'in' operator to search for '$$links' in a7fa2819-9c71-4e9e-ba4e-c17296536cda
    at getSelected (/usr/src/jellyfish/apps/server/node_modules/@balena/jellyfish-core/build/kernel.js:76:19)
    at getSelected (/usr/src/jellyfish/apps/server/node_modules/@balena/jellyfish-core/build/kernel.js:90:33)
    at getSelected (/usr/src/jellyfish/apps/server/node_modules/@balena/jellyfish-core/build/kernel.js:90:33)
    at getSelected (/usr/src/jellyfish/apps/server/node_modules/@balena/jellyfish-core/build/kernel.js:90:33)
    at /usr/src/jellyfish/apps/server/node_modules/@balena/jellyfish-core/build/kernel.js:138:38
    at Generator.next (<anonymous>)
    at /usr/src/jellyfish/apps/server/node_modules/@balena/jellyfish-core/build/kernel.js:8:71
    at new Promise (<anonymous>)
    at __awaiter (/usr/src/jellyfish/apps/server/node_modules/@balena/jellyfish-core/build/kernel.js:4:12)
    at getQueryFromSchema (/usr/src/jellyfish/apps/server/node_modules/@balena/jellyfish-core/build/kernel.js:125:73)
    at Kernel.<anonymous> (/usr/src/jellyfish/apps/server/node_modules/@balena/jellyfish-core/build/kernel.js:618:55)
    at Generator.next (<anonymous>)
    at /usr/src/jellyfish/apps/server/node_modules/@balena/jellyfish-core/build/kernel.js:8:71
    at new Promise (<anonymous>)
    at __awaiter (/usr/src/jellyfish/apps/server/node_modules/@balena/jellyfish-core/build/kernel.js:4:12)
    at Kernel.query (/usr/src/jellyfish/apps/server/node_modules/@balena/jellyfish-core/build/kernel.js:617:16)
    at /usr/src/jellyfish/apps/server/build/http/facades/query.js:48:47
    at tryCatcher (/usr/src/jellyfish/apps/server/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/usr/src/jellyfish/apps/server/node_modules/bluebird/js/release/promise.js:547:31)
    at Promise._settlePromise (/usr/src/jellyfish/apps/server/node_modules/bluebird/js/release/promise.js:604:18)
    at Promise._settlePromise0 (/usr/src/jellyfish/apps/server/node_modules/bluebird/js/release/promise.js:649:10)
    at Promise._settlePromises (/usr/src/jellyfish/apps/server/node_modules/bluebird/js/release/promise.js:729:18)
    at _drainQueueStep (/usr/src/jellyfish/apps/server/node_modules/bluebird/js/release/async.js:93:12)
    at _drainQueue (/usr/src/jellyfish/apps/server/node_modules/bluebird/js/release/async.js:86:9)
    at Async._drainQueues (/usr/src/jellyfish/apps/server/node_modules/bluebird/js/release/async.js:102:5)
    at Immediate.Async.drainQueues (/usr/src/jellyfish/apps/server/node_modules/bluebird/js/release/async.js:15:14)
    at processImmediate (internal/timers.js:464:21)
    at process.topLevelDomainCallback (domain.js:147:15)
    at process.callbackTrampoline (internal/async_hooks.js:129:24)"}

CVE-2021-23368 (Medium) detected in postcss-7.0.35.tgz - autoclosed

CVE-2021-23368 - Medium Severity Vulnerability

Vulnerable Library - postcss-7.0.35.tgz

Tool for transforming styles with JS plugins

Library home page: https://registry.npmjs.org/postcss/-/postcss-7.0.35.tgz

Dependency Hierarchy:

depcheck-1.4.0.tgz (Root Library)
- compiler-sfc-3.0.5.tgz
  - ❌ postcss-7.0.35.tgz (Vulnerable Library)

Found in HEAD commit: d88fa280faa5d22992032c6f000f9257017af553

Found in base branch: master

Vulnerability Details

The package postcss from 7.0.0 and before 8.2.10 are vulnerable to Regular Expression Denial of Service (ReDoS) during source map parsing.

Publish Date: 2021-04-12

URL: CVE-2021-23368

CVSS 3 Score Details (5.3)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Network
- Attack Complexity: Low
- Privileges Required: None
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: None
- Integrity Impact: None
- Availability Impact: Low

For more information on CVSS3 Scores, click here.

Suggested Fix

Type: Upgrade version

Origin: https://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2021-23368

Release Date: 2021-04-12

Fix Resolution: postcss -8.2.10

Step up your Open Source Security Game with WhiteSource here

CVE-2021-23343 (High) detected in path-parse-1.0.6.tgz - autoclosed

CVE-2021-23343 - High Severity Vulnerability

Vulnerable Library - path-parse-1.0.6.tgz

Node.js path.parse() ponyfill

Library home page: https://registry.npmjs.org/path-parse/-/path-parse-1.0.6.tgz

Dependency Hierarchy:

depcheck-1.4.1.tgz (Root Library)
- resolve-1.20.0.tgz
  - ❌ path-parse-1.0.6.tgz (Vulnerable Library)

Found in HEAD commit: 4ddbb6ce0ea8ea6c1b98c74f10a00dba35404baa

Found in base branch: master

Vulnerability Details

All versions of package path-parse are vulnerable to Regular Expression Denial of Service (ReDoS) via splitDeviceRe, splitTailRe, and splitPathRe regular expressions. ReDoS exhibits polynomial worst-case time complexity.

Publish Date: 2021-05-04

URL: CVE-2021-23343

CVSS 3 Score Details (7.5)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Network
- Attack Complexity: Low
- Privileges Required: None
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: None
- Integrity Impact: None
- Availability Impact: High

For more information on CVSS3 Scores, click here.

Step up your Open Source Security Game with WhiteSource here

Support queries on version_* fields

It should be possible to individually filter/query on all version_* fields.
cards being returned from the backend should contain the computed version field as well as all version_* fields
querying by exactly version should be possible additionally by using the version field

CVE-2020-7598 (Medium) detected in minimist-0.0.10.tgz - autoclosed

CVE-2020-7598 - Medium Severity Vulnerability

Vulnerable Library - minimist-0.0.10.tgz

parse argument options

Library home page: https://registry.npmjs.org/minimist/-/minimist-0.0.10.tgz

Dependency Hierarchy:

lint-5.4.2.tgz (Root Library)
- coffeelint-1.16.2.tgz
  - optimist-0.6.1.tgz
    - ❌ minimist-0.0.10.tgz (Vulnerable Library)

Found in HEAD commit: 85acfb7bc2e4ca1a412a81a78299d5b04c8fd59f

Found in base branch: master

Vulnerability Details

minimist before 1.2.2 could be tricked into adding or modifying properties of Object.prototype using a "constructor" or "proto" payload.

Publish Date: 2020-03-11

URL: CVE-2020-7598

CVSS 3 Score Details (5.6)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Network
- Attack Complexity: High
- Privileges Required: None
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: Low
- Integrity Impact: Low
- Availability Impact: Low

For more information on CVSS3 Scores, click here.

Suggested Fix

Type: Upgrade version

Origin: https://github.com/substack/minimist/commit/63e7ed05aa4b1889ec2f3b196426db4500cbda94

Release Date: 2020-03-11

Fix Resolution: minimist - 0.2.1,1.2.3

Step up your Open Source Security Game with WhiteSource here

Dependency Dashboard

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Rate-Limited

These updates are currently rate-limited. Click on a checkbox below to force their creation now.

Update @types/bluebird from 3.5.37 to 3.5.38
Update @types/node from 18.11.9 to 18.11.10
🔐 Create all rate-limited PRs at once 🔐

Open

These updates have all been created already. Click a checkbox below to force a retry/rebase of any.

Update product-os/flowzone action from v0.39.11 to v0.40.5

Ignored or Blocked

These are blocked by an existing closed PR and will not be recreated unless you click a checkbox below.

Update dependencies (external major bumps)

Detected dependencies

docker-compose

docker-compose.test.yml

docker-compose.yml

redis 7

dockerfile

Dockerfile

github-actions

.github/workflows/codeql-analysis.yml

actions/checkout v3

github/codeql-action v2

github/codeql-action v2

github/codeql-action v2

.github/workflows/flowzone.yml

product-os/flowzone v0.39.11

npm

package.json

@balena/jellyfish-assert ^1.2.106

@balena/jellyfish-environment ^14.6.14

@balena/jellyfish-logger ^5.1.126

@balena/jellyfish-metrics ^2.0.297

bluebird ^3.7.2

commander ^9.4.1

fast-equals ^4.0.3

fast-json-patch ^3.1.1

json-e ^4.4.3

json-schema ^0.4.0

json-schema-deref-sync ^0.14.0

json-schema-to-typescript ^11.0.2

lodash ^4.17.21

pg ^8.8.0

pg-format ^1.0.4

redis 4.5.1

redis-mock ^0.56.3

semver ^7.3.8

skhema ^6.0.6

stopword ^2.0.5

traverse ^0.6.7

typed-error ^3.2.1

uuid ^9.0.0

uuid-v4-regex ^1.0.2

@balena/lint ^6.2.1

@json-schema-org/tests ^2.0.0

@types/bluebird ^3.5.37

@types/jest ^29.2.3

@types/json-schema ^7.0.11

@types/node ^18.11.9

@types/pg ^8.6.5

@types/pg-format ^1.0.2

@types/redis-mock ^0.17.1

@types/semver ^7.3.13

@types/traverse ^0.6.32

@types/uuid ^8.3.4

deplint ^1.1.3

jest ^29.3.1

lint-staged ^13.0.4

prettier ^2.8.0

rimraf ^3.0.2

simple-git-hooks ^2.8.1

ts-jest ^29.0.3

ts-node ^10.9.1

typedoc ^0.23.21

typescript ^4.9.3

Check this box to trigger a request for Renovate to run again on this repository

Slow queries (2021-08-19)

Query 1

SDK query:

await window.sdk.query({type:'object',properties:{type:{const:'[email protected]'}}},{limit:1,sortBy:'created_at',sortDir:'desc'})

Compiled SQL:

SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]') ORDER BY cards.created_at DESC NULLS LAST LIMIT 1;

Production EXPLAIN ANALYZE results:

 Limit  (cost=7822735.98..7822735.98 rows=1 width=40) (actual time=599543.460..599543.461 rows=1 loops=1)
   ->  Sort  (cost=7822735.98..7856030.25 rows=13317711 width=40) (actual time=599543.459..599543.460 rows=1 loops=1)
         Sort Key: cards.created_at DESC NULLS LAST
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Index Scan using link__name__idx on cards  (cost=0.56..7756147.42 rows=13317711 width=40) (actual time=0.090..591071.241 rows=26786791 loops=1)
               Filter: (NOT (SubPlan 1))
               SubPlan 1
                 ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=26786791)
                       Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 6.350 ms
 Execution Time: 599543.494 ms
(11 rows)

Local EXPLAIN ANALYZE results:

 Limit  (cost=30.20..30.20 rows=1 width=40) (actual time=0.939..0.941 rows=1 loops=1)
   ->  Sort  (cost=30.20..30.21 rows=4 width=40) (actual time=0.938..0.939 rows=1 loops=1)
         Sort Key: cards.created_at DESC NULLS LAST
         Sort Method: top-N heapsort  Memory: 28kB
         ->  Bitmap Heap Scan on cards  (cost=4.34..30.18 rows=4 width=40) (actual time=0.151..0.908 rows=11 loops=1)
               Recheck Cond: (type = '[email protected]'::text)
               Filter: (NOT (SubPlan 1))
               Heap Blocks: exact=7
               ->  Bitmap Index Scan on type_cards_idx  (cost=0.00..4.34 rows=9 width=0) (actual time=0.030..0.031 rows=11 loops=1)
                     Index Cond: (type = '[email protected]'::text)
               SubPlan 1
                 ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.003..0.003 rows=0 loops=11)
                       Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 1.819 ms
 Execution Time: 1.045 ms
(15 rows)

Cause and Solution

This seems to be caused by the compiled SQL specifying DESC NULLS LAST while our current indexes are defined with DESC only and not DESC NULLS LAST. We should probably update our existing created_at and updated_at (and other columns commonly used with sortBy if there are any) index definitions to include NULLS LAST. Seems like it would also be a good idea to create new ASC NULLS LAST indexes for these columns.

Current created_at index:

 CREATE INDEX created_at_cards_idx ON public.cards USING btree (created_at DESC);

New index added for testing defined with DESC NULLS LAST (deleted after testing, test results noted below):

 CREATE INDEX created_at_desc_nulls_last_cards_idx ON public.cards USING btree (created_at DESC NULLS LAST);

Production EXPLAIN ANALYZE results with new index in place:

 Limit  (cost=0.57..1.36 rows=1 width=40) (actual time=0.055..0.056 rows=1 loops=1)
   ->  Index Scan using created_at_desc_nulls_last_cards_idx on cards  (cost=0.57..10570319.90 rows=13362502 width=40) (actual time=0.055..0.055 rows=1 loops=1)
         Filter: ((type = '[email protected]'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
 Planning Time: 3.553 ms
 Execution Time: 0.075 ms
(8 rows)

Query 2

SDK query:

await window.sdk.query({"type":"object","anyOf":[{"$$links":{"is bookmarked by":{"type":"object","required":["type","id"],"properties":{"type":{"const":"[email protected]"},"id":{"const":"c0144404-5ecd-4af1-ae19-a55255ff778c"}}}}},{"$$links":{"has attached element":{"type":"object"}}},true],"properties":{"slug":{"const":"repository-product-os-jellyfish"},"version":{"const":"1.0.0"}}},{"links":{"has attached element":{"limit":20,"sortBy":"created_at","sortDir":"desc"}}})

Compiled SQL:

SELECT to_jsonb(cards) || jsonb_build_object('links', jsonb_build_object('is bookmarked by', "linked@/is bookmarked by".linkedCards, 'has attached element', "linked@/has attached element".linkedCards), 'version', to_jsonb(CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') ))) AS payload FROM cards, ( WITH fence AS MATERIALIZED ( SELECT array_agg( row( cards.id, array[ row(cards.id, 0, "join@/is bookmarked by.0".id)::linkEdge, row(cards.id, 1, "join@/has attached element.0".id)::linkEdge ] )::cardAndLinkEdges ) AS arr FROM cards LEFT JOIN links AS "links@/is bookmarked by.0" ON ( "links@/is bookmarked by.0".name = 'is bookmarked by' AND "links@/is bookmarked by.0".fromId = cards.id ) OR ( "links@/is bookmarked by.0".inversename = 'is bookmarked by' AND "links@/is bookmarked by.0".toId = cards.id ) LEFT JOIN cards AS "join@/is bookmarked by.0" ON ( ( "links@/is bookmarked by.0".name = 'is bookmarked by' AND "links@/is bookmarked by.0".toId = "join@/is bookmarked by.0".id ) OR ( "links@/is bookmarked by.0".inversename = 'is bookmarked by' AND "links@/is bookmarked by.0".fromId = "join@/is bookmarked by.0".id ) ) AND ( (NOT (EXISTS (SELECT 1 FROM unnest("join@/is bookmarked by.0".markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND "join@/is bookmarked by.0".type = '[email protected]' AND "join@/is bookmarked by.0".id = 'c0144404-5ecd-4af1-ae19-a55255ff778c') ) LEFT JOIN links AS "links@/has attached element.0" ON ( "links@/has attached element.0".name = 'has attached element' AND "links@/has attached element.0".fromId = cards.id ) OR ( "links@/has attached element.0".inversename = 'has attached element' AND "links@/has attached element.0".toId = cards.id ) LEFT JOIN cards AS "join@/has attached element.0" ON ( ( "links@/has attached element.0".name = 'has attached element' AND "links@/has attached element.0".toId = "join@/has attached element.0".id ) OR ( "links@/has attached element.0".inversename = 'has attached element' AND "links@/has attached element.0".fromId = "join@/has attached element.0".id ) ) AND ( NOT (EXISTS (SELECT 1 FROM unnest("join@/has attached element.0".markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) ) WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.slug = 'repository-product-os-jellyfish' AND CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') ) = '1.0.0') GROUP BY cards.id LIMIT 1000 ) SELECT unaggregated.cardId, array( SELECT row( edges.source, edges.sink, array_agg(edges.idx) )::polyLinkEdge FROM unnest(unaggregated.edges) AS edges GROUP BY edges.source, edges.sink ) AS linkEdges FROM ( SELECT unwrapped.cardId, array_agg(unwrapped.edges) AS edges FROM ( SELECT (unnest(fence.arr)).* FROM fence ) AS unwrapped GROUP BY unwrapped.cardId ) AS unaggregated ) AS main, LATERAL ( SELECT orderedEdges.source, coalesce( array_agg( (SELECT CASE WHEN 0 = any(orderedEdges.idxs) THEN to_jsonb(linked) || jsonb_build_object('version', to_jsonb(CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', linked.version_major, linked.version_minor, linked.version_patch), NULLIF(linked.version_prerelease, '') ), NULLIF(linked.version_build, '') ))) ELSE '{}'::jsonb END ) ORDER BY orderedEdges.seq ) FILTER (WHERE linked.id IS NOT NULL), '{}'::jsonb[] ) AS linkedCards FROM ( SELECT edges.source, edges.sink, edges.idxs, row_number() OVER (PARTITION BY edges.source) AS seq FROM unnest(main.linkEdges) AS edges LEFT JOIN cards AS linked ON linked.id = edges.sink WHERE edges.idxs && ARRAY[0] ) AS orderedEdges LEFT JOIN cards AS linked ON linked.id = orderedEdges.sink GROUP BY orderedEdges.source ) AS "linked@/is bookmarked by", LATERAL ( SELECT orderedEdges.source, coalesce( array_agg( (SELECT CASE WHEN 1 = any(orderedEdges.idxs) THEN to_jsonb(linked) || jsonb_build_object('version', to_jsonb(CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', linked.version_major, linked.version_minor, linked.version_patch), NULLIF(linked.version_prerelease, '') ), NULLIF(linked.version_build, '') ))) ELSE '{}'::jsonb END ) ORDER BY orderedEdges.seq ) FILTER (WHERE linked.id IS NOT NULL), '{}'::jsonb[] ) AS linkedCards FROM ( SELECT edges.source, edges.sink, edges.idxs, row_number() OVER (PARTITION BY edges.source ORDER BY linked.created_at DESC NULLS LAST) AS seq FROM unnest(main.linkEdges) AS edges LEFT JOIN cards AS linked ON linked.id = edges.sink WHERE edges.idxs && ARRAY[1] ) AS orderedEdges LEFT JOIN cards AS linked ON (linked.id = orderedEdges.sink AND orderedEdges.seq <= 20) GROUP BY orderedEdges.source ) AS "linked@/has attached element" WHERE cards.id = main.cardId LIMIT 1000;

Production EXPLAIN ANALYZE results:

 Limit  (cost=111.30..226.69 rows=10 width=32) (actual time=28066.538..28067.477 rows=1 loops=1)
   ->  Nested Loop  (cost=111.30..226.69 rows=10 width=32) (actual time=28066.537..28067.476 rows=1 loops=1)
         ->  Nested Loop  (cost=107.81..168.09 rows=10 width=716) (actual time=27316.826..27317.760 rows=1 loops=1)
               ->  Nested Loop  (cost=107.11..137.74 rows=10 width=684) (actual time=27296.691..27297.621 rows=1 loops=1)
                     ->  Subquery Scan on unaggregated  (cost=106.54..109.79 rows=10 width=48) (actual time=27296.653..27297.581 rows=1 loops=1)
                           CTE fence
                             ->  Limit  (cost=106.14..106.16 rows=1 width=48) (actual time=26358.300..26358.312 rows=1 loops=1)
                                   ->  GroupAggregate  (cost=106.14..106.16 rows=1 width=48) (actual time=26358.299..26358.310 rows=1 loops=1)
                                         Group Key: cards_1.id
                                         ->  Sort  (cost=106.14..106.15 rows=1 width=48) (actual time=26019.587..26049.372 rows=496980 loops=1)
                                               Sort Key: cards_1.id
                                               Sort Method: quicksort  Memory: 51115kB
                                               ->  Nested Loop Left Join  (cost=11.25..106.13 rows=1 width=48) (actual time=15.253..25909.133 rows=496980 loops=1)
                                                     ->  Nested Loop Left Join  (cost=8.73..101.32 rows=1 width=94) (actual time=15.208..1019.102 rows=496980 loops=1)
                                                           ->  Nested Loop Left Join  (cost=4.61..10.26 rows=1 width=32) (actual time=0.246..0.404 rows=9 loops=1)
                                                                 Join Filter: ((("links@/is bookmarked by.0".name = 'is bookmarked by'::text) AND ("links@/is bookmarked by.0".toid = "join@/is bookmarked by.0".id)) OR (("links@/is bookmarked by.0".inversename = 'is bookmarked by'::text) AND ("links@/is bookmarked by.0".fromid = "join@/is bookmarked by.0".id)))
                                                                 Rows Removed by Join Filter: 8
                                                                 ->  Nested Loop Left Join  (cost=4.04..7.43 rows=1 width=78) (actual time=0.227..0.272 rows=9 loops=1)
                                                                       ->  Index Scan using cards_slug_version_key on cards cards_1  (cost=0.69..2.95 rows=1 width=16) (actual time=0.195..0.205 rows=1 loops=1)
                                                                             Index Cond: ((slug)::text = 'repository-product-os-jellyfish'::text)
                                                                             Filter: ((concat_ws('+'::text, concat_ws('-'::text, concat_ws('.'::text, version_major, version_minor, version_patch), NULLIF(version_prerelease, ''::text)), NULLIF(version_build, ''::text)) = '1.0.0'::text) AND (NOT (SubPlan 3)))
                                                                             SubPlan 3
                                                                               ->  Function Scan on unnest contents_2  (cost=0.00..0.15 rows=8 width=0) (actual time=0.004..0.004 rows=0 loops=1)
                                                                                     Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                                                                       ->  Bitmap Heap Scan on links "links@/is bookmarked by.0"  (cost=3.35..4.47 rows=1 width=62) (actual time=0.030..0.057 rows=9 loops=1)
                                                                             Recheck Cond: (((fromid = cards_1.id) AND (name = 'is bookmarked by'::text)) OR ((toid = cards_1.id) AND (inversename = 'is bookmarked by'::text)))
                                                                             Heap Blocks: exact=9
                                                                             ->  BitmapOr  (cost=3.35..3.35 rows=1 width=0) (actual time=0.025..0.026 rows=0 loops=1)
                                                                                   ->  Bitmap Index Scan on idx_links_fromid_name  (cost=0.00..1.67 rows=1 width=0) (actual time=0.013..0.014 rows=9 loops=1)
                                                                                         Index Cond: ((fromid = cards_1.id) AND (name = 'is bookmarked by'::text))
                                                                                   ->  Bitmap Index Scan on idx_links_toid_inversename  (cost=0.00..1.67 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)
                                                                                         Index Cond: ((toid = cards_1.id) AND (inversename = 'is bookmarked by'::text))
                                                                 ->  Index Scan using cards_pkey on cards "join@/is bookmarked by.0"  (cost=0.57..2.81 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=9)
                                                                       Index Cond: (id = 'c0144404-5ecd-4af1-ae19-a55255ff778c'::uuid)
                                                                       Filter: ((type = '[email protected]'::text) AND (NOT (SubPlan 1)))
                                                                       SubPlan 1
                                                                         ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (actual time=0.002..0.002 rows=0 loops=9)
                                                                               Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                                                           ->  Bitmap Heap Scan on links "links@/has attached element.0"  (cost=4.12..90.66 rows=40 width=62) (actual time=13.861..105.519 rows=55220 loops=9)
                                                                 Recheck Cond: (((fromid = cards_1.id) AND (name = 'has attached element'::text)) OR ((toid = cards_1.id) AND (inversename = 'has attached element'::text)))
                                                                 Heap Blocks: exact=391122
                                                                 ->  BitmapOr  (cost=4.12..4.12 rows=77 width=0) (actual time=7.879..7.879 rows=0 loops=9)
                                                                       ->  Bitmap Index Scan on idx_links_fromid_name  (cost=0.00..1.67 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=9)
                                                                             Index Cond: ((fromid = cards_1.id) AND (name = 'has attached element'::text))
                                                                       ->  Bitmap Index Scan on idx_links_toid_inversename  (cost=0.00..2.43 rows=77 width=0) (actual time=7.867..7.867 rows=55220 loops=9)
                                                                             Index Cond: ((toid = cards_1.id) AND (inversename = 'has attached element'::text))
                                                     ->  Bitmap Heap Scan on cards "join@/has attached element.0"  (cost=2.52..4.80 rows=1 width=16) (actual time=0.019..0.049 rows=1 loops=496980)
                                                           Recheck Cond: (("links@/has attached element.0".toid = id) OR ("links@/has attached element.0".fromid = id))
                                                           Filter: (((("links@/has attached element.0".name = 'has attached element'::text) AND ("links@/has attached element.0".toid = id)) OR (("links@/has attached element.0".inversename = 'has attached element'::text) AND ("links@/has attached element.0".fromid = id))) AND (NOT (SubPlan 2)))
                                                           Rows Removed by Filter: 1
                                                           Heap Blocks: exact=993960
                                                           ->  BitmapOr  (cost=2.52..2.52 rows=2 width=0) (actual time=0.009..0.009 rows=0 loops=496980)
                                                                 ->  Bitmap Index Scan on cards_pkey  (cost=0.00..1.26 rows=1 width=0) (actual time=0.004..0.004 rows=57 loops=496980)
                                                                       Index Cond: (id = "links@/has attached element.0".toid)
                                                                 ->  Bitmap Index Scan on cards_pkey  (cost=0.00..1.26 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=496980)
                                                                       Index Cond: (id = "links@/has attached element.0".fromid)
                                                           SubPlan 2
                                                             ->  Function Scan on unnest contents_1  (cost=0.00..0.15 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=496980)
                                                                   Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                           ->  HashAggregate  (cost=0.38..0.50 rows=10 width=48) (actual time=26741.447..26741.449 rows=1 loops=1)
                                 Group Key: ((unnest(fence.arr))).cardid
                                 ->  Result  (cost=0.00..0.23 rows=10 width=48) (actual time=26503.944..26607.509 rows=496980 loops=1)
                                       ->  ProjectSet  (cost=0.00..0.08 rows=10 width=32) (actual time=26503.937..26545.055 rows=496980 loops=1)
                                             ->  CTE Scan on fence  (cost=0.00..0.02 rows=1 width=32) (actual time=26503.932..26503.934 rows=1 loops=1)
                           SubPlan 5
                             ->  HashAggregate  (cost=0.18..0.30 rows=10 width=64) (actual time=505.520..543.177 rows=55222 loops=1)
                                   Group Key: edges_2.source, edges_2.sink
                                   ->  Function Scan on unnest edges_2  (cost=0.00..0.10 rows=10 width=36) (actual time=131.048..244.760 rows=993960 loops=1)
                     ->  Index Scan using cards_pkey on cards  (cost=0.57..2.79 rows=1 width=668) (actual time=0.033..0.033 rows=1 loops=1)
                           Index Cond: (id = unaggregated.cardid)
               ->  GroupAggregate  (cost=0.71..3.02 rows=1 width=48) (actual time=20.132..20.135 rows=1 loops=1)
                     Group Key: edges.source
                     ->  Nested Loop Left Join  (cost=0.71..2.95 rows=1 width=724) (actual time=18.735..19.878 rows=2 loops=1)
                           ->  WindowAgg  (cost=0.14..0.16 rows=1 width=72) (actual time=18.618..19.755 rows=2 loops=1)
                                 ->  Sort  (cost=0.14..0.14 rows=1 width=64) (actual time=18.573..18.574 rows=2 loops=1)
                                       Sort Key: edges.source
                                       Sort Method: quicksort  Memory: 1966kB
                                       ->  Function Scan on unnest edges  (cost=0.00..0.13 rows=1 width=64) (actual time=14.953..18.013 rows=2 loops=1)
                                             Filter: (idxs && '{0}'::integer[])
                                             Rows Removed by Filter: 55220
                           ->  Index Scan using cards_pkey on cards linked  (cost=0.57..2.79 rows=1 width=668) (actual time=0.056..0.056 rows=0 loops=2)
                                 Index Cond: (id = edges.sink)
                     SubPlan 6
                       ->  Result  (cost=0.00..0.04 rows=1 width=32) (actual time=0.068..0.069 rows=1 loops=1)
         ->  GroupAggregate  (cost=3.49..5.82 rows=1 width=48) (actual time=749.366..749.369 rows=1 loops=1)
               Group Key: edges_1.source
               ->  Nested Loop Left Join  (cost=3.49..5.75 rows=1 width=724) (actual time=367.481..743.812 rows=55220 loops=1)
                     Join Filter: ((row_number() OVER (?)) <= 20)
                     Rows Removed by Join Filter: 55200
                     ->  WindowAgg  (cost=2.92..2.95 rows=1 width=80) (actual time=367.457..405.796 rows=55220 loops=1)
                           ->  Sort  (cost=2.92..2.93 rows=1 width=72) (actual time=367.447..377.278 rows=55220 loops=1)
                                 Sort Key: edges_1.source, linked_2.created_at DESC NULLS LAST
                                 Sort Method: quicksort  Memory: 9302kB
                                 ->  Nested Loop Left Join  (cost=0.57..2.91 rows=1 width=72) (actual time=5.238..325.133 rows=55220 loops=1)
                                       ->  Function Scan on unnest edges_1  (cost=0.00..0.13 rows=1 width=64) (actual time=5.215..27.559 rows=55220 loops=1)
                                             Filter: (idxs && '{1}'::integer[])
                                             Rows Removed by Filter: 2
                                       ->  Index Scan using cards_pkey on cards linked_2  (cost=0.57..2.79 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=55220)
                                             Index Cond: (id = edges_1.sink)
                     ->  Index Scan using cards_pkey on cards linked_1  (cost=0.57..2.79 rows=1 width=668) (actual time=0.006..0.006 rows=1 loops=55220)
                           Index Cond: (id = edges_1.sink)
               SubPlan 7
                 ->  Result  (cost=0.00..0.04 rows=1 width=32) (actual time=0.015..0.016 rows=1 loops=20)
 Planning Time: 21.476 ms
 Execution Time: 28153.188 ms
(105 rows)

Local EXPLAIN ANALYZE results:

 Limit  (cost=65.43..377.30 rows=10 width=32) (actual time=0.130..0.134 rows=0 loops=1)
   ->  Nested Loop  (cost=65.43..377.30 rows=10 width=32) (actual time=0.130..0.133 rows=0 loops=1)
         ->  Nested Loop  (cost=56.68..208.14 rows=10 width=895) (actual time=0.129..0.132 rows=0 loops=1)
               ->  Nested Loop  (cost=56.27..122.28 rows=10 width=863) (actual time=0.129..0.131 rows=0 loops=1)
                     ->  Subquery Scan on unaggregated  (cost=56.00..59.25 rows=10 width=48) (actual time=0.129..0.131 rows=0 loops=1)
                           CTE fence
                             ->  Limit  (cost=55.60..55.62 rows=1 width=48) (actual time=0.114..0.115 rows=0 loops=1)
                                   ->  GroupAggregate  (cost=55.60..55.62 rows=1 width=48) (actual time=0.113..0.115 rows=0 loops=1)
                                         Group Key: cards_1.id
                                         ->  Sort  (cost=55.60..55.60 rows=1 width=48) (actual time=0.113..0.114 rows=0 loops=1)
                                               Sort Key: cards_1.id
                                               Sort Method: quicksort  Memory: 25kB
                                               ->  Nested Loop Left Join  (cost=9.11..55.59 rows=1 width=48) (actual time=0.054..0.056 rows=0 loops=1)
                                                     ->  Nested Loop Left Join  (cost=0.55..40.04 rows=1 width=128) (actual time=0.054..0.055 rows=0 loops=1)
                                                           Join Filter: ((("links@/has attached element.0".name = 'has attached element'::text) AND ("links@/has attached element.0".fromid = cards_1.id)) OR (("links@/has attached element.0".inversename = 'has attached element'::text) AND ("links@/has attached element.0".toid = cards_1.id)))
                                                           ->  Nested Loop Left Join  (cost=0.55..28.35 rows=1 width=32) (actual time=0.054..0.055 rows=0 loops=1)
                                                                 Join Filter: ((("links@/is bookmarked by.0".name = 'is bookmarked by'::text) AND ("links@/is bookmarked by.0".toid = "join@/is bookmarked by.0".id)) OR (("links@/is bookmarked by.0".inversename = 'is bookmarked by'::text) AND ("links@/is bookmarked by.0".fromid = "join@/is bookmarked by.0".id)))
                                                                 ->  Nested Loop Left Join  (cost=0.27..20.02 rows=1 width=112) (actual time=0.054..0.054 rows=0 loops=1)
                                                                       Join Filter: ((("links@/is bookmarked by.0".name = 'is bookmarked by'::text) AND ("links@/is bookmarked by.0".fromid = cards_1.id)) OR (("links@/is bookmarked by.0".inversename = 'is bookmarked by'::text) AND ("links@/is bookmarked by.0".toid = cards_1.id)))
                                                                       ->  Index Scan using slug_cards_idx on cards cards_1  (cost=0.27..8.33 rows=1 width=16) (actual time=0.053..0.054 rows=0 loops=1)
                                                                             Index Cond: ((slug)::text = 'repository-product-os-jellyfish'::text)
                                                                             Filter: ((concat_ws('+'::text, concat_ws('-'::text, concat_ws('.'::text, version_major, version_minor, version_patch), NULLIF(version_prerelease, ''::text)), NULLIF(version_build, ''::text)) = '1.0.0'::text) AND (NOT (SubPlan 3)))
                                                                             SubPlan 3
                                                                               ->  Function Scan on unnest contents_2  (cost=0.00..0.15 rows=8 width=0) (never executed)
                                                                                     Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                                                                       ->  Seq Scan on links "links@/is bookmarked by.0"  (cost=0.00..11.65 rows=2 width=96) (never executed)
                                                                             Filter: ((name = 'is bookmarked by'::text) OR (inversename = 'is bookmarked by'::text))
                                                                 ->  Index Scan using cards_pkey on cards "join@/is bookmarked by.0"  (cost=0.27..8.31 rows=1 width=16) (never executed)
                                                                       Index Cond: (id = 'c0144404-5ecd-4af1-ae19-a55255ff778c'::uuid)
                                                                       Filter: ((type = '[email protected]'::text) AND (NOT (SubPlan 1)))
                                                                       SubPlan 1
                                                                         ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0) (never executed)
                                                                               Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                                                           ->  Seq Scan on links "links@/has attached element.0"  (cost=0.00..11.65 rows=2 width=96) (never executed)
                                                                 Filter: ((name = 'has attached element'::text) OR (inversename = 'has attached element'::text))
                                                     ->  Bitmap Heap Scan on cards "join@/has attached element.0"  (cost=8.56..15.54 rows=1 width=16) (never executed)
                                                           Recheck Cond: (("links@/has attached element.0".toid = id) OR ("links@/has attached element.0".fromid = id))
                                                           Filter: (((("links@/has attached element.0".name = 'has attached element'::text) AND ("links@/has attached element.0".toid = id)) OR (("links@/has attached element.0".inversename = 'has attached element'::text) AND ("links@/has attached element.0".fromid = id))) AND (NOT (SubPlan 2)))
                                                           ->  BitmapOr  (cost=8.56..8.56 rows=2 width=0) (never executed)
                                                                 ->  Bitmap Index Scan on cards_pkey  (cost=0.00..4.28 rows=1 width=0) (never executed)
                                                                       Index Cond: (id = "links@/has attached element.0".toid)
                                                                 ->  Bitmap Index Scan on cards_pkey  (cost=0.00..4.28 rows=1 width=0) (never executed)
                                                                       Index Cond: (id = "links@/has attached element.0".fromid)
                                                           SubPlan 2
                                                             ->  Function Scan on unnest contents_1  (cost=0.00..0.15 rows=8 width=0) (never executed)
                                                                   Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))
                           ->  HashAggregate  (cost=0.38..0.50 rows=10 width=48) (actual time=0.128..0.129 rows=0 loops=1)
                                 Group Key: ((unnest(fence.arr))).cardid
                                 ->  Result  (cost=0.00..0.23 rows=10 width=48) (actual time=0.115..0.115 rows=0 loops=1)
                                       ->  ProjectSet  (cost=0.00..0.08 rows=10 width=32) (actual time=0.115..0.115 rows=0 loops=1)
                                             ->  CTE Scan on fence  (cost=0.00..0.02 rows=1 width=32) (actual time=0.114..0.114 rows=0 loops=1)
                           SubPlan 5
                             ->  HashAggregate  (cost=0.18..0.30 rows=10 width=64) (never executed)
                                   Group Key: edges_2.source, edges_2.sink
                                   ->  Function Scan on unnest edges_2  (cost=0.00..0.10 rows=10 width=36) (never executed)
                     ->  Index Scan using cards_pkey on cards  (cost=0.27..6.29 rows=1 width=847) (never executed)
                           Index Cond: (id = unaggregated.cardid)
               ->  GroupAggregate  (cost=0.41..8.57 rows=1 width=48) (never executed)
                     Group Key: edges.source
                     ->  Nested Loop Left Join  (cost=0.41..8.50 rows=1 width=903) (never executed)
                           ->  WindowAgg  (cost=0.14..0.16 rows=1 width=72) (never executed)
                                 ->  Sort  (cost=0.14..0.14 rows=1 width=64) (never executed)
                                       Sort Key: edges.source
                                       ->  Function Scan on unnest edges  (cost=0.00..0.13 rows=1 width=64) (never executed)
                                             Filter: (idxs && '{0}'::integer[])
                           ->  Index Scan using cards_pkey on cards linked  (cost=0.27..8.29 rows=1 width=847) (never executed)
                                 Index Cond: (id = edges.sink)
                     SubPlan 6
                       ->  Result  (cost=0.00..0.04 rows=1 width=32) (never executed)
         ->  GroupAggregate  (cost=8.75..16.87 rows=1 width=48) (never executed)
               Group Key: edges_1.source
               ->  Nested Loop Left Join  (cost=8.75..16.81 rows=1 width=903) (never executed)
                     Join Filter: ((row_number() OVER (?)) <= 20)
                     ->  WindowAgg  (cost=8.47..8.50 rows=1 width=80) (never executed)
                           ->  Sort  (cost=8.47..8.48 rows=1 width=72) (never executed)
                                 Sort Key: edges_1.source, linked_2.created_at DESC NULLS LAST
                                 ->  Nested Loop Left Join  (cost=0.28..8.46 rows=1 width=72) (never executed)
                                       ->  Function Scan on unnest edges_1  (cost=0.00..0.13 rows=1 width=64) (never executed)
                                             Filter: (idxs && '{1}'::integer[])
                                       ->  Index Scan using cards_pkey on cards linked_2  (cost=0.27..8.29 rows=1 width=24) (never executed)
                                             Index Cond: (id = edges_1.sink)
                     ->  Index Scan using cards_pkey on cards linked_1  (cost=0.27..8.29 rows=1 width=847) (never executed)
                           Index Cond: (id = edges_1.sink)
               SubPlan 7
                 ->  Result  (cost=0.00..0.04 rows=1 width=32) (never executed)
 Planning Time: 15.384 ms
 Execution Time: 0.644 ms
(87 rows)

Cause and Solution

Not sure of a solution yet, but found that this bit may be causing the query to take so long:

->  CTE Scan on fence  (cost=0.00..0.02 rows=1 width=32) (actual time=26503.932..26503.934 rows=1 loops=1)

Deleting contract results in error

Trying to delete the following contract:

{
  "id": "36ac5095-30d9-41e4-9130-a1811bb8ebe1",
  "data": {
    "user": "joshua3",
    "origin": "[email protected]",
    "status": "open",
    "mirrors": [
      "https://api.typeform.com/forms/NLYdaT/responses?included_response_ids=fc77y7bamuhgfor9negfc77y71srmmuv"
    ],
    "timestamp": "2020-09-11T18:26:22.000Z",
    "howWouldYouDescribeYourRole": "App Developer",
    "howHasYourExperienceBeenSoFar": "Good overall.\nI appreciate that the first 10 devices are free which allows full testing without worrying about a trial time limit.\n\nMissing features I would like:\n- Email alerts when a device goes offline or has an issue\n- Integration with github to build and deploy updates\n- Set environment variables for each device tag",
    "couldYouBrieflyDescribeYourUsecase": "To manage and push updates to remote devices for cyber security monitoring (Suricata, Filebeat, and related)",
    "howDidYouFirstHearAboutBalenaCloud": "google search for open source remote device update system",
    "howLikelyAreYouToRecommendBalenaCloud": 8
  },
  "name": "",
  "slug": "form-response-NLYdaT-fc77y7bamuhgfor9negfc77y71srmmuv",
  "tags": [],
  "type": "[email protected]",
  "links": {},
  "active": true,
  "markers": [],
  "version": "1.0.0",
  "requires": [],
  "linked_at": {
    "has attached element": "2021-03-11T09:41:42.135Z"
  },
  "created_at": "2020-09-11T18:26:22.000Z",
  "updated_at": "2021-03-11T09:39:07.117Z",
  "capabilities": []
}

Results in the following error being logged:

Error: slug format invalid: [email protected]\n at Object.exports.parseVersionedSlug (/usr/src/jellyfish/apps/action-server/node_modules/@balena/jellyfish-core/lib/backend/postgres/utils.js:129:9)\n at Object.exports.getBySlug (/usr/src/jellyfish/apps/action-server/node_modules/@balena/jellyfish-core/lib/backend/postgres/cards.js:260:12)\n at PostgresBackend.getElementBySlug (/usr/src/jellyfish/apps/action-server/node_modules/@balena/jellyfish-core/lib/backend/postgres/index.js:665:30)\n at /usr/src/jellyfish/apps/action-server/node_modules/@balena/jellyfish-core/lib/kernel.js:524:41\n at Task.<anonymous> (/usr/src/jellyfish/apps/action-server/node_modules/@balena/jellyfish-core/lib/backend/postgres/index.js:566:11)\n at callback (/usr/src/jellyfish/apps/action-server/node_modules/pg-promise/lib/task.js:201:21)\n at /usr/src/jellyfish/apps/action-server/node_modules/pg-promise/lib/task.js:261:25\n at runMicrotasks (<anonymous>)\n at processTicksAndRejections (internal/process/task_queues.js:93:5)\n at async measureAsync (/usr/src/jellyfish/apps/action-server/node_modules/@balena/jellyfish-core/node_modules/@balena/jellyfish-metrics/lib/index.js:38:17)\n at async /usr/src/jellyfish/apps/action-server/node_modules/@balena/jellyfish-core/node_modules/@balena/jellyfish-metrics/lib/index.js:342:18\n at async Object.exports.measureCardPatch (/usr/src/jellyfish/apps/action-server/node_modules/@balena/jellyfish-core/node_modules/@balena/jellyfish-metrics/lib/index.js:506:17)\n at async Kernel.patchCardBySlug (/usr/src/jellyfish/apps/action-server/node_modules/@balena/jellyfish-core/lib/kernel.js:515:18)\n at async commit (/usr/src/jellyfish/apps/action-server/node_modules/@balena/jellyfish-worker/lib/executor.js:80:23)\n at async handler (/usr/src/jellyfish/apps/action-server/node_modules/@balena/jellyfish-action-library/lib/actions/action-delete-card.js:25:17)\n at async Worker.execute (/usr/src/jellyfish/apps/action-server/node_modules/@balena/jellyfish-worker/lib/index.js:656:18)\n at async /usr/src/jellyfish/apps/action-server/lib/bootstrap.js:330:4\n at async actionRequest (/usr/src/jellyfish/apps/action-server/node_modules/@balena/jellyfish-queue/lib/consumer.js:90:8)\n at async doNext (/usr/src/jellyfish/apps/action-server/node_modules/graphile-worker/dist/worker.js:183:17)"

I did an experiment and found that the regex being used in utils.parseVersionedSlug will not match uppercase characters, which appears to be causing the error.

Handle deadlock on concurrent index creation

Handle deadlock errors caused by simultaneous CREATE INDEX CONCURRENTLY executions with try/catch.

CVE-2020-7608 (Medium) detected in yargs-parser-11.1.1.tgz

CVE-2020-7608 - Medium Severity Vulnerability

Vulnerable Library - yargs-parser-11.1.1.tgz

the mighty option parser used by yargs

Library home page: https://registry.npmjs.org/yargs-parser/-/yargs-parser-11.1.1.tgz

Dependency Hierarchy:

deplint-1.1.2.tgz (Root Library)
- hawthorn-1.2.6.tgz
  - yargs-12.0.5.tgz
    - ❌ yargs-parser-11.1.1.tgz (Vulnerable Library)

Found in HEAD commit: cae9133bca33d1b1fa03ae812124e9b5e0262211

Found in base branch: master

Vulnerability Details

yargs-parser could be tricked into adding or modifying properties of Object.prototype using a "proto" payload.

Publish Date: 2020-03-16

URL: CVE-2020-7608

CVSS 3 Score Details (5.3)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Local
- Attack Complexity: Low
- Privileges Required: Low
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: Low
- Integrity Impact: Low
- Availability Impact: Low

For more information on CVSS3 Scores, click here.

Suggested Fix

Type: Upgrade version

Origin: https://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2020-7608

Release Date: 2020-03-16

Fix Resolution: v18.1.1;13.1.2;15.0.1

Step up your Open Source Security Game with WhiteSource here

Conditional `$$links`

Tracking issue for the conditional $$links feature, introduced in product-os/jellyfish#4286.

$$links inside an allOf results in a silent schema corruption, caused by json-schema-merge-allof
All links are implemented as outer joins + null checks as this is a more general method. Need to investigate if it's a good idea to optimize required links to use inner joins instead

Migrate to TypeScript

Need to migrate this package, probably bit by bit, to TypeScript.

Use Postgres error codes

pg-promise error objects include the Postgres error code when errors occur. This error code should be used when checking for specific errors instead of pattern matching against the error message.

Optional links query doesn't return anything

The below query doesn't return anything although the anyOf contains true and the id exists. Removing the links stuff returns the one contract as expected

{
    "type": "object",
    "properties": {
        "id": {
            "const": "b681c25a-7bd9-4b24-b2cc-c540287c8950"
        }
    },
    "anyOf": [
        {
            "$$links": {
                "was built into": {
                    "type": "object",
                    "anyOf": [
                        {
                            "$$links": {
                                "was built into": {
                                    "type": "object",
                                    "anyOf": [
                                        {
                                            "$$links": {
                                                "was built into": {
                                                    "type": "object"
                                                }
                                            }
                                        },
                                        {
                                            "$$links": {
                                                "was merged as": {
                                                    "type": "object"
                                                }
                                            }
                                        },
                                        true
                                    ]
                                }
                            }
                        },
                        {
                            "$$links": {
                                "was merged as": {
                                    "type": "object"
                                }
                            }
                        },
                        true
                    ]
                }
            }
        },
        {
            "$$links": {
                "was merged as": {
                    "type": "object"
                }
            }
        },
        true
    ]
}

CVE-2021-23362 (Medium) detected in hosted-git-info-2.8.8.tgz

CVE-2021-23362 - Medium Severity Vulnerability

Vulnerable Library - hosted-git-info-2.8.8.tgz

Provides metadata and conversions from repository urls for Github, Bitbucket and Gitlab

Library home page: https://registry.npmjs.org/hosted-git-info/-/hosted-git-info-2.8.8.tgz

Dependency Hierarchy:

jest-26.6.3.tgz (Root Library)
- core-26.6.3.tgz
  - jest-resolve-26.6.2.tgz
    - read-pkg-up-7.0.1.tgz
      - read-pkg-5.2.0.tgz
        
        normalize-package-data-2.5.0.tgz
        
        ❌ hosted-git-info-2.8.8.tgz (Vulnerable Library)

Found in HEAD commit: acc582766979e2cf42d2081baac4ca20fd10441e

Found in base branch: master

Vulnerability Details

The package hosted-git-info before 3.0.8 are vulnerable to Regular Expression Denial of Service (ReDoS) via regular expression shortcutMatch in the fromUrl function in index.js. The affected regular expression exhibits polynomial worst-case time complexity.

Publish Date: 2021-03-23

URL: CVE-2021-23362

CVSS 3 Score Details (5.3)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Network
- Attack Complexity: Low
- Privileges Required: None
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: None
- Integrity Impact: None
- Availability Impact: Low

For more information on CVSS3 Scores, click here.

Suggested Fix

Type: Upgrade version

Origin: GHSA-43f8-2h32-f4cj

Release Date: 2021-03-23

Fix Resolution: hosted-git-info - 2.8.9,3.0.8

Step up your Open Source Security Game with WhiteSource here

[Sentry] JellyfishInvalidSlug: The primary key is too long: link-message-27d4833b-2be0-4c36-966b-594a467a5b2e-is-attached-to-iss...

https://sentry.io/organizations/balena/issues/2530064911/?referrer=webhooks_plugin

Generated by the "productOS sentry->GitHub" zap on zapier.com

Nested optional links don't work as expected

Given the following query with two anyOf options:

contract = { id: "70f0b3cf-9489-49b0-9c98-19dafbacdea4" }
q = {
		type: 'object',
		required: ['id', 'type', 'slug'],
		properties: {
			id: {
				type: 'string',
			},
			slug: {
				type: 'string',
			},
			type: {
				const: '[email protected]',
				type: 'string',
			},
		},
		anyOf: [
			{
				$$links: {
					'is of': {
						type: 'object',
						properties: {
							type: {
								const: '[email protected]',
							},
						},
						$$links: {
							'is used by': {
								type: 'object',
								properties: {
									id: {
										const: contract.id,
									},
								},
							},
						},
					},
				},
			},
			{
				$$links: {
					'is of': {
						type: 'object',
						properties: {
							id: {
								const: contract.id,
							},
						},
					},
				},
			},
		],
	};
await sdk.query(q)

Only results for the first branch of the anyOf are returned. Anything that matches the second branch is ignored.
Removing the nested $$links in the first branch will make the query work as expected, though this isn't ideal as it will return the wrong values.

Inefficient data.mirrors index

(Related to #867)

Found the following index for data.mirrors. From what I can tell, it is inefficient and should instead:

Be a type index - partial index only for types that actually have a data.mirrors field
Use same path selector used in SELECT statements (data#>'{mirrors}' instead of data->'mirrors')

Couldn't find a query that would actually use the current index. The new test index was used as expected with much lower planner costs.

data_mirrors_cards_idx | CREATE INDEX data_mirrors_cards_idx ON public.cards USING gin (((data -> 'mirrors'::text)) jsonb_path_ops)

The most common place queries against the data.mirrors field is made by integrations during mirrors/translates using getElementByMirrorId(). The query used includes type, making the case for switching to multiple partial indexes (by type) that much stronger.

Tried the following queries to check query plans/costs on the production database:

Query 1

await window.sdk.query({
  type: 'object',
  required: [ 'data' ],
  properties: {
    data: {
      type: 'object',
      required: [ 'mirrors' ],
      properties: {
        mirrors: {
          type: 'array',
          contains: {
            const: 'test'
          }
        }
      }
    }
  }
})

Compiled SQL and EXPLAIN SELECT results:

jellyfish=> explain SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"mirrors"}') = 'array' AND cards.data#>'{"mirrors"}' @> '"test"') LIMIT 1000;
                                                                                               QUERY PLAN                                                                                               
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..8912790.20 rows=1 width=32)
   ->  Seq Scan on cards  (cost=0.00..8912790.20 rows=1 width=32)
         Filter: (((data #> '{mirrors}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{mirrors}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))

Query 2

await window.sdk.query({
  type: 'object',
  required: [ 'type', 'data' ],
  properties: {
    type: {
      type: 'string',
      const: '[email protected]'
    },
    data: {
      type: 'object',
      required: [ 'mirrors' ],
      properties: {
        mirrors: {
          type: 'array',
          contains: {
            const: 'test'
          }
        }
      }
    }
  }
})

Compiled SQL and EXPLAIN SELECT results:

jellyfish=> explain SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"mirrors"}') = 'array' AND cards.data#>'{"mirrors"}' @> '"test"') LIMIT 1000;
                                                                                               QUERY PLAN                                                                                               
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..640.07 rows=1 width=32)
   ->  Index Scan using "support-thread__data_status__idx" on cards  (cost=0.29..640.07 rows=1 width=32)
         Filter: (((data #> '{mirrors}'::text[]) @> '"test"'::jsonb) AND (jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{mirrors}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))

Query 2 with new index

New index, partial index with updated field selector to match SELECT statement:

CREATE INDEX CONCURRENTLY "t_support-thread__data_mirrors__idx" ON public.cards USING gin (((data #> '{mirrors}'::text[]))) WHERE (type = '[email protected]'::text);

Compiled SQL and EXPLAIN SELECT results:

jellyfish=> explain SELECT to_jsonb(cards) || jsonb_build_object('version', CONCAT_WS('+', CONCAT_WS('-', CONCAT_WS('.', cards.version_major, cards.version_minor, cards.version_patch), NULLIF(cards.version_prerelease, '') ), NULLIF(cards.version_build, '') )) AS payload FROM cards WHERE (NOT (EXISTS (SELECT 1 FROM unnest(cards.markers) AS contents WHERE NOT ((contents IN ('user-jellyfish', 'org-balena') OR (contents)::text ~ E'(^|\\+)(user-jellyfish|org-balena)($|\\+)')))) AND cards.type = '[email protected]' AND jsonb_typeof(cards.data) = 'object' AND jsonb_typeof(cards.data#>'{"mirrors"}') = 'array' AND cards.data#>'{"mirrors"}' @> '"test"') LIMIT 1000;
                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3.30..7.92 rows=1 width=32)
   ->  Bitmap Heap Scan on cards  (cost=3.30..7.92 rows=1 width=32)
         Recheck Cond: (((data #> '{mirrors}'::text[]) @> '"test"'::jsonb) AND (type = '[email protected]'::text))
         Filter: ((jsonb_typeof(data) = 'object'::text) AND (jsonb_typeof((data #> '{mirrors}'::text[])) = 'array'::text) AND (NOT (SubPlan 1)))
         ->  Bitmap Index Scan on "t_support-thread__data_mirrors__idx"  (cost=0.00..3.30 rows=4 width=0)
               Index Cond: ((data #> '{mirrors}'::text[]) @> '"test"'::jsonb)
         SubPlan 1
           ->  Function Scan on unnest contents  (cost=0.00..0.15 rows=8 width=0)
                 Filter: ((contents <> ALL ('{user-jellyfish,org-balena}'::text[])) AND (contents !~ '(^|\+)(user-jellyfish|org-balena)($|\+)'::text))

Update from Jellyfish!

CVE-2020-8203 (High) detected in lodash-4.17.11.tgz

CVE-2020-8203 - High Severity Vulnerability

Vulnerable Library - lodash-4.17.11.tgz

Lodash modular utilities.

Library home page: https://registry.npmjs.org/lodash/-/lodash-4.17.11.tgz

Dependency Hierarchy:

@balena/jellyfish-logger-0.0.221.tgz (Root Library)
- le_node-1.8.0.tgz
  - ❌ lodash-4.17.11.tgz (Vulnerable Library)

Found in HEAD commit: cae9133bca33d1b1fa03ae812124e9b5e0262211

Found in base branch: master

Vulnerability Details

Prototype pollution attack when using _.zipObjectDeep in lodash before 4.17.20.

Publish Date: 2020-07-15

URL: CVE-2020-8203

CVSS 3 Score Details (7.4)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Network
- Attack Complexity: High
- Privileges Required: None
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: None
- Integrity Impact: High
- Availability Impact: High

For more information on CVSS3 Scores, click here.

Suggested Fix

Type: Upgrade version

Origin: https://www.npmjs.com/advisories/1523

Release Date: 2020-07-23

Fix Resolution: lodash - 4.17.19

Step up your Open Source Security Game with WhiteSource here

SQL-side filtering

Tracking issue for the SQL-side filtering feature, introduced in product-os/jellyfish#4286.

For linked cards with the same link type declared in different $$links entries, the properties selected for that linked card are derived from an unspecified mix of all declarations with that link type: #84
Combining conditional $$links and additionalProperties: false in an anyOf branch, recursively, is not supported

CVE-2020-28500 (Medium) detected in lodash-4.17.20.tgz - autoclosed

CVE-2020-28500 - Medium Severity Vulnerability

Vulnerable Library - lodash-4.17.20.tgz

Lodash modular utilities.

Library home page: https://registry.npmjs.org/lodash/-/lodash-4.17.20.tgz

Dependency Hierarchy:

❌ lodash-4.17.20.tgz (Vulnerable Library)

Found in HEAD commit: 9c508fbbab629dad8348bf09ae4bd73e0ec6500f

Found in base branch: master

Vulnerability Details

All versions of package lodash; all versions of package org.fujion.webjars:lodash are vulnerable to Regular Expression Denial of Service (ReDoS) via the toNumber, trim and trimEnd functions. Steps to reproduce (provided by reporter Liyuan Chen): var lo = require('lodash'); function build_blank (n) { var ret = "1" for (var i = 0; i < n; i++) { ret += " " } return ret + "1"; } var s = build_blank(50000) var time0 = Date.now(); lo.trim(s) var time_cost0 = Date.now() - time0; console.log("time_cost0: " + time_cost0) var time1 = Date.now(); lo.toNumber(s) var time_cost1 = Date.now() - time1; console.log("time_cost1: " + time_cost1) var time2 = Date.now(); lo.trimEnd(s) var time_cost2 = Date.now() - time2; console.log("time_cost2: " + time_cost2)

Publish Date: 2021-02-15

URL: CVE-2020-28500

CVSS 3 Score Details (5.3)

Base Score Metrics:

Exploitability Metrics:
- Attack Vector: Network
- Attack Complexity: Low
- Privileges Required: None
- User Interaction: None
- Scope: Unchanged
Impact Metrics:
- Confidentiality Impact: None
- Integrity Impact: None
- Availability Impact: Low

For more information on CVSS3 Scores, click here.

Step up your Open Source Security Game with WhiteSource here

Dependency Dashboard

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Rate-Limited

These updates are currently rate-limited. Click on a checkbox below to force their creation now.

Update dependency node to 18.19
Update dependency @types/node to v20
Update dependency commander to v11
Update dependency lint-staged to v15
Update dependency node to v20
Update dependency stopword to v3
Update github/codeql-action action to v3
Lock file maintenance
🔐 Create all rate-limited PRs at once 🔐

Open

These updates have all been created already. Click a checkbox below to force a retry/rebase of any.

Update github/codeql-action digest to 2f93e43
Update dependency redis to v4.6.12
Update actions/checkout action to v4
Click on this checkbox to rebase all open PRs at once

Detected dependencies

docker-compose

docker-compose.test.yml

docker-compose.yml

redis 7

dockerfile

Dockerfile

docker/dockerfile 1

node 20

github-actions

.github/workflows/codeql-analysis.yml

actions/checkout v3@f43a0e5ff2bd294095638e18286ca9a3d1956744

github/codeql-action v2@0116bc2df50751f9724a2e35ef1f24d22f90e4e1

github/codeql-action v2@0116bc2df50751f9724a2e35ef1f24d22f90e4e1

github/codeql-action v2@0116bc2df50751f9724a2e35ef1f24d22f90e4e1

.github/workflows/flowzone.yml

npm

package.json

@balena/jellyfish-assert ^1.11.0

@balena/jellyfish-environment ^21.0.0

@balena/jellyfish-logger ^6.0.1

bluebird ^3.7.2

commander ^10.0.0

fast-equals ^5.0.1

fast-json-patch ^3.1.1

json-e ^4.5.3

json-schema ^0.4.0

json-schema-deref-sync ^0.14.0

json-schema-to-typescript ^13.0.0

lodash ^4.17.21

pg ^8.10.0

pg-format ^1.0.4

redis 4.6.10

redis-mock ^0.56.3

semver ^7.5.4

skhema ^6.0.6

stopword ^2.0.8

traverse ^0.6.7

typed-error ^3.2.2

uuid-v4-regex ^1.0.2

@balena/lint ^7.0.2

@json-schema-org/tests ^2.0.0

@types/bluebird ^3.5.38

@types/jest ^29.5.0

@types/json-schema ^7.0.11

@types/node ^18.15.11

@types/pg ^8.6.6

@types/pg-format ^1.0.2

@types/redis-mock ^0.17.1

@types/semver ^7.5.0

@types/stopword ^2.0.0

@types/traverse ^0.6.32

deplint ^1.1.3

jest ^29.5.0

lint-staged ^14.0.1

prettier ^3.0.2

rimraf ^5.0.1

simple-git-hooks ^2.9.0

ts-jest ^29.0.5

ts-node ^10.9.1

typescript ^5.2.2

node >=16.0.0

nvm

.nvmrc

node 18.18

Add metrics to measure index creation times

Recommend Projects

React

A declarative, efficient, and flexible JavaScript library for building user interfaces.
Vue.js

🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript

TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
TensorFlow

An Open Source Machine Learning Framework for Everyone
Django

The Web framework for perfectionists with deadlines.
Laravel

A PHP framework for web artisans
D3

Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

javascript

JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
web

Some thing interesting about web. New door for the world.
server

A server is a program made to process requests and deliver data to clients.
Machine learning

Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Visualization

Some thing interesting about visualization, use data art
Game

Some thing interesting about game, make everyone happy.

Recommend Org

Facebook

We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft

Open source projects and samples from Microsoft.
Google

Google ❤️ Open Source for everyone.
Alibaba

Alibaba Open Source for everyone
D3

Data-Driven Documents codes.
Tencent

China tencent open source team.

product-os / autumndb Goto Github PK

autumndb's People

Contributors

Stargazers

Watchers

Forkers

autumndb's Issues

CVE-2021-23358 - High Severity Vulnerability

CVE-2021-23337 - High Severity Vulnerability

support-thread__data_status__idx

message__data_readBy__idx

CVE-2021-29469 - Medium Severity Vulnerability

CVE-2019-10744 - High Severity Vulnerability

WS-2020-0163 - Medium Severity Vulnerability

Rate-Limited

Open

Detected dependencies

CVE-2020-7774 - High Severity Vulnerability

Improperly defined type indexes

Current definition

Proposed definition

Query

Compiled SQL

EXPLAIN ANALYZE SELECT results on production

Current definition

Proposed definition

Query

Compiled SQL

EXPLAIN ANALYZE SELECT results on production

Current definition

Proposed definition

Query

Compiled SQL

EXPLAIN ANALYZE SELECT results on production

Current definition

Proposed definition

Query

Compiled SQL

EXPLAIN ANALYZE SELECT results on production

Current definition

Proposed definition

Query

Compiled SQL

EXPLAIN ANALYZE SELECT results on production

Current definition

Proposed definition

Query

Compiled SQL

EXPLAIN ANALYZE SELECT results on production

Current definition

Proposed definition

Query

Compiled SQL

EXPLAIN ANALYZE SELECT results on production

Current definition

Proposed definition

Query

Compiled SQL

EXPLAIN ANALYZE SELECT results on production

Current definition

Proposed definition

Query

Compiled SQL

EXPLAIN ANALYZE SELECT results on production

Current definition

Proposed definition

Query

Compiled SQL

EXPLAIN ANALYZE SELECT results on production

Current definition

Proposed definition

Query

Compiled SQL

EXPLAIN ANALYZE SELECT results on production

Current definition

Proposed definition

Query

Compiled SQL

EXPLAIN ANALYZE SELECT results on production

Current definition

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production

`EXPLAIN ANALYZE SELECT` results on production