prisma: Very slow nested queries compared to Prisma1
Bug description
Deeply nested queries are very slow compared to Prisma1. The example shown below was taking 500-800ms on Prisma1, on Prisma2 it takes 1.5-3s.
How to reproduce
- The schema was sent to schemas@prisma.io just now. We are using a POSTGRESQL DB, and running
2.0.0-beta.5. - The resolver is simple:
export const initiativeConfigsQuery = queryField('initiativeConfigs', {
type: 'InitiativeConfig',
list: true,
async resolve(parent, args, ctx) {
const id = getUserId(ctx)
return ctx.prisma.initiativeConfig.findMany({
where: { project: { users: { some: { id } } } },
})
},
})
- The query not so much:
client query
{
initiativeConfigs {
id
initiativeLayout {
id
groups {
id
labelKey
blocks {
id
labelKey
classes
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
chart {
id
}
blocks {
id
labelKey
classes
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
chart {
id
}
blocks {
id
labelKey
classes
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
chart {
id
}
blocks {
id
labelKey
classes
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
chart {
id
}
blocks {
id
labelKey
classes
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
chart {
id
}
blocks {
id
labelKey
classes
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
chart {
id
}
primitives {
componentType
layoutShape {
defaultClass
defaultColor
rules {
order
class
color
rule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
}
}
selectType
imageUrl
textPrimitiveType
displayType
lockingRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
labelKey
property {
pRules {
labelKey
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
formulaOnUpdate
hierarchy {
id
}
optionGroup {
id
}
defaultValue {
booleanValue
dateValue
numberValue
textValue
optionValue {
id
}
}
pType
description
labelKey
id
}
componentType
id
}
}
primitives {
componentType
layoutShape {
defaultClass
defaultColor
rules {
order
class
color
rule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
}
}
selectType
imageUrl
textPrimitiveType
displayType
lockingRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
labelKey
property {
pRules {
labelKey
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
formulaOnUpdate
hierarchy {
id
}
optionGroup {
id
}
defaultValue {
booleanValue
dateValue
numberValue
textValue
optionValue {
id
}
}
pType
description
labelKey
id
}
componentType
id
}
}
primitives {
componentType
layoutShape {
defaultClass
defaultColor
rules {
order
class
color
rule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
}
}
selectType
imageUrl
textPrimitiveType
displayType
lockingRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
labelKey
property {
pRules {
labelKey
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
formulaOnUpdate
hierarchy {
id
}
optionGroup {
id
}
defaultValue {
booleanValue
dateValue
numberValue
textValue
optionValue {
id
}
}
pType
description
labelKey
id
}
componentType
id
}
}
primitives {
componentType
layoutShape {
defaultClass
defaultColor
rules {
order
class
color
rule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
}
}
selectType
imageUrl
textPrimitiveType
displayType
lockingRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
labelKey
property {
pRules {
labelKey
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
formulaOnUpdate
hierarchy {
id
}
optionGroup {
id
}
defaultValue {
booleanValue
dateValue
numberValue
textValue
optionValue {
id
}
}
pType
description
labelKey
id
}
componentType
id
}
}
primitives {
componentType
layoutShape {
defaultClass
defaultColor
rules {
order
class
color
rule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
}
}
selectType
imageUrl
textPrimitiveType
displayType
lockingRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
labelKey
property {
pRules {
labelKey
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
formulaOnUpdate
hierarchy {
id
}
optionGroup {
id
}
defaultValue {
booleanValue
dateValue
numberValue
textValue
optionValue {
id
}
}
pType
description
labelKey
id
}
componentType
id
}
}
primitives {
componentType
layoutShape {
defaultClass
defaultColor
rules {
order
class
color
rule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
}
}
selectType
imageUrl
textPrimitiveType
displayType
lockingRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
labelKey
property {
pRules {
labelKey
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
formulaOnUpdate
hierarchy {
id
}
optionGroup {
id
}
defaultValue {
booleanValue
dateValue
numberValue
textValue
optionValue {
id
}
}
pType
description
labelKey
id
}
componentType
id
}
}
indicatorTable {
defaultColumnWidth
defaultPeriodType {
id
}
property {
hierarchy {
id
labelKey
root {
id
}
elements {
id
labelKey
children {
id
}
order
indicator {
id
labelKey
unitLabelKey
periodType {
id
}
hierarchyElement {
id
}
isSummable
allowAggregatedInput
}
}
}
}
}
childInitiativesProperty {
childConfig {
id
}
id
}
hideTab
classes
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
}
mainLayoutShape {
defaultClass
defaultColor
rules {
order
class
color
rule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
}
}
initiativeName {
componentType
layoutShape {
defaultClass
defaultColor
rules {
order
class
color
rule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
}
}
selectType
imageUrl
textPrimitiveType
displayType
lockingRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
visibilityRule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
labelKey
property {
pRules {
labelKey
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
formulaOnUpdate
hierarchy {
id
}
optionGroup {
id
}
defaultValue {
booleanValue
dateValue
numberValue
textValue
optionValue {
id
}
}
pType
description
labelKey
id
}
componentType
id
}
}
listLayout {
id
columns {
id
labelKey
order
defaultWidth
defaultVisibility
cType
property1 {
id
optionGroup {
id
}
hierarchy {
id
}
}
}
}
preview {
title {
id
}
description {
id
}
layoutShape {
defaultClass
defaultColor
rules {
order
class
color
rule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
}
}
}
entityLabel {
key
}
tileScreen {
title
rows
columns
config {
preview {
title {
id
}
description {
id
}
layoutShape {
defaultClass
defaultColor
rules {
order
class
color
rule {
property {
id
}
hierarchyElement {
id
}
option {
id
}
optionValue
dateMin
dateMax
regex
boolean
numberMin
numberMax
use
check
}
}
}
}
}
tiles {
entity {
id
}
area
}
}
kanbanProperties {
id
pType
labelKey
optionGroup {
id
}
}
}
}
Expected behavior
The query duration should be shorter.
Environment & setup
- OS: Windows 10
- Database: Postgres
- Prisma:
2.0.0-beta.5 - Node.js: 12.13.1
About this issue
- Original URL
- State: closed
- Created 4 years ago
- Reactions: 4
- Comments: 17 (10 by maintainers)
I added the dump to Slack!