PostgresQL `$queryRawUnsafe` performance issue when combining array filters and `CASE WHEN`

Bug description

I got a performance issue when doing a very specific raw query. Please find attached a code example that performed the same request in 4 different ways:

• QUERY PG: Using pg node package
• QUERY A: Using $queryRawUnsafe without any parameters
• QUERY B: Using $queryRawUnsafe with parameters but without CASE WHEN
• QUERY C: Using $queryRawUnsafe with parameters

The QUERY C take 10x more time than other calls.

The QUERY C for a reason I do not understand triggers a full PostgreSQL table scan and does not use indexes ( other queries do use the indexed). ( Verified using a SELECT * FROM pg_stats_activity while the QUERY C is running )

The queried table has ~ 8 Millions records

How to reproduce

Code to reproduce the issue

import * as console from 'console'
import * as dotenv from 'dotenv'
import * as pg from 'pg'

import { PrismaClient } from '@/prisma'

dotenv.config({ path: '.env' })

async function main() {
	const client = new PrismaClient({
		datasources: { db: { url: process.env.DATABASE_URL } },
		log: ['query'],
	})
	await client.$connect()

	const pgClient = new pg.Client({ connectionString: process.env.DATABASE_URL })
	await pgClient.connect()

	const tableName = 'messages_v2_20230525'

	//
	// Query using 'pg' package using postgres parameters
	//
	console.time('QUERY PG')
	await pgClient.query(
		`
			SELECT uuid
			FROM ${tableName}
			WHERE
				CASE WHEN $1::uuid IS NOT NULL THEN company_uuid = $1::uuid ELSE TRUE END
				AND CASE WHEN $2::"Classification"[] IS NOT NULL THEN classifications && $2::"Classification"[] ELSE TRUE END
				AND CASE WHEN $3::language_code[] IS NOT NULL THEN language = ANY($3::language_code[]) ELSE TRUE END
			ORDER BY published_at DESC
			LIMIT 100
		`,
		['a8ad1fe4-6892-43e6-b070-0466d7eff904', ['INSULT'], ['de']],
	)
	console.timeEnd('QUERY PG')

	//
	// Query using prisma with inlined parameters
	//
	console.time('QUERY A')
	await client.$queryRawUnsafe(
		`
			SELECT uuid
			FROM ${tableName}
			WHERE
				CASE WHEN 'a8ad1fe4-6892-43e6-b070-0466d7eff904'::uuid IS NOT NULL THEN company_uuid = 'a8ad1fe4-6892-43e6-b070-0466d7eff904'::uuid ELSE TRUE END
				AND CASE WHEN '{INSULT}'::"Classification"[] IS NOT NULL THEN classifications && '{INSULT}'::"Classification"[] ELSE TRUE END
				AND CASE WHEN '{de}'::language_code[] IS NOT NULL THEN language = ANY('{de}'::language_code[]) ELSE TRUE END
			ORDER BY published_at DESC
			LIMIT 100
		`,
	)
	console.timeEnd('QUERY A')

	//
	// Query using prisma and postgres parameters but without CASE WHEN
	//
	console.time('QUERY B')
	await client.$queryRawUnsafe(
		`
			SELECT uuid
			FROM ${tableName}
			WHERE
				company_uuid = $1::uuid
				AND classifications && $2::"Classification"[]
				AND language = ANY($3::language_code[])
			ORDER BY published_at DESC
			LIMIT 100
		`,
		'a8ad1fe4-6892-43e6-b070-0466d7eff904',
		['INSULT'],
		['de'],
	)
	console.timeEnd('QUERY B')

	//
	// Query using prisma and postgres parameters with CASE WHEN
	//
	console.time('QUERY C')
	await client.$queryRawUnsafe(
		`
			SELECT uuid
			FROM ${tableName}
			WHERE
				CASE WHEN $1::uuid IS NOT NULL THEN company_uuid = $1::uuid ELSE TRUE END
				AND CASE WHEN $2::"Classification"[] IS NOT NULL THEN classifications && $2::"Classification"[] ELSE TRUE END
				AND CASE WHEN $3::language_code[] IS NOT NULL THEN language = ANY($3::language_code[]) ELSE TRUE END
			ORDER BY published_at DESC
			LIMIT 100
		`,
		'a8ad1fe4-6892-43e6-b070-0466d7eff904',
		['INSULT'],
		['de'],
	)
	console.timeEnd('QUERY C')

	await client.$disconnect()
	await pgClient.end()
}

main().catch(console.error)

Query response time

QUERY PG: 377.7ms
QUERY A: 225.048ms
QUERY B: 3.560s
QUERY C: 49.687s

Full logs

QUERY PG: 377.7ms
prisma:query 
            SELECT uuid
            FROM messages_v2_20230525
            WHERE
              CASE WHEN 'a8ad1fe4-6892-43e6-b070-0466d7eff904'::uuid IS NOT NULL THEN company_uuid = 'a8ad1fe4-6892-43e6-b070-0466d7eff904'::uuid ELSE TRUE END
              AND CASE WHEN '{INSULT}'::"Classification"[] IS NOT NULL THEN classifications && '{INSULT}'::"Classification"[] ELSE TRUE END
              AND CASE WHEN '{de}'::language_code[] IS NOT NULL THEN language = ANY('{de}'::language_code[]) ELSE TRUE END
            ORDER BY published_at DESC
            LIMIT 100
                
QUERY A: 225.048ms
prisma:query 
            SELECT uuid
            FROM messages_v2_20230525
            WHERE
                company_uuid = $1::uuid
              AND classifications && $2::"Classification"[]
              AND language = ANY($3::language_code[])
            ORDER BY published_at DESC
            LIMIT 100
                
QUERY B: 3.560s
prisma:query 
            SELECT uuid
            FROM messages_v2_20230525
            WHERE
              CASE WHEN $1::uuid IS NOT NULL THEN company_uuid = $1::uuid ELSE TRUE END
              AND CASE WHEN $2::"Classification"[] IS NOT NULL THEN classifications && $2::"Classification"[] ELSE TRUE END
              AND CASE WHEN $3::language_code[] IS NOT NULL THEN language = ANY($3::language_code[]) ELSE TRUE END
            ORDER BY published_at DESC
            LIMIT 100
                
QUERY C: 49.687s

Expected behavior

The QUERY C should not take 10x longer than other query to complete

Prisma information

Using $queryRawUnsafe schema is not relevant

Environment & setup

• OS: Darwin 22.5.0 Darwin Kernel Version 22.5.0 arm64 (Can be reproduced on linux amd64)
• Database: PostgreSQL:14
• Node.js version: v18.16.0

Prisma Version

prisma                  : 4.15.0
@prisma/client          : 4.15.0
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 8fbc245156db7124f997f4cecdd8d1219e360944 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli 8fbc245156db7124f997f4cecdd8d1219e360944 (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.15.0-28.8fbc245156db7124f997f4cecdd8d1219e360944
Default Engines Hash    : 8fbc245156db7124f997f4cecdd8d1219e360944
Studio                  : 0.484.0

Start with the supplied TypeScript reproduction at $queryRawUnsafe and run QUERY PG, QUERY A, QUERY B, and QUERY C against PostgreSQL 14. Compare the execution plans and parameter handling for the CASE WHEN predicates; done means identifying whether Prisma's parameterized raw-query path causes the plan regression and establishing the appropriate fix or limitation.