Skip to content

sql: dropping objects has a quadratic time complexity in relation to the number of objects #50783

@RichardJCai

Description

@RichardJCai

Dropping 1 table takes 7 roundtrips, 2 takes 16 and 3 takes 27. (See below for ddl analysis snippet)
I think think there is a n^2 relationship between number of objects dropped using a drop statement and the number of objects being dropped.

This comes from the fact that every dropped object results in a call to createOrUpdateSchemaChangeJob which does a loop through the the current evalContext's jobs to check if a job has been queued up or not for the drop, each check performs a round trip because it queries for the job (SELECT payload, progress FROM system.jobs WHERE id = $1) and queues a job if one doesn't exist.

This means if for the n'th object being dropped, it will do n - 1 queries (each a roundtrip) to find if a job has been queued or not for it.

# DropTable
exec
CREATE TABLE t1()
----

count
DROP TABLE t1
----
7

exec
CREATE TABLE t1();
CREATE TABLE t2();
----

count
DROP TABLE t1,t2
----
16

exec
CREATE TABLE t1();
CREATE TABLE t2();
CREATE TABLE t3();
----

count
DROP TABLE t1,t2,t3
----
27

Metadata

Metadata

Assignees

Labels

A-schema-changesC-performancePerf of queries or internals. Solution not expected to change functional behavior.

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions