r/PostgreSQL • • 9d ago

Help Me! Query optimization

Moving my project from MS Sql to PostrgesSql,

Need to run mass update query, ClientDelivery table has 1,054,155 records

In MsSql query takes about 10 seconds, In PostgresSql it actually never finishes because i am canceling it after waiting for 5 minutes. Records are identical in both databases. I am running queries in PgAdmin and SqlManagment client.

Any reason for such huge difference? I understand i am doing table scan but 1 million records is not that much.

/*MSSQL*/
UPDATE tblClientDelivery
SET routeId=t1.routeId
FROM tblClientDelivery t
INNER JOIN tblClients t1 ON t.clientId=t1.id
WHERE t.routeId=0

/*PostgreSql*/
UPDATE client_delivery
SET route_id=t1.route_id
FROM client_delivery t
INNER JOIN clients t1 ON t.client_id=t1.id
WHERE t.route_id=0
27 Upvotes

16 comments sorted by

41

u/elevarq 9d ago

The PostgreSQL query isn’t equivalent to the SQL Server version. In PostgreSQL, repeating client_delivery in FROM introduces a separate table reference, and you never join it to the actual update target. Your WHERE clause therefore filters the source rows, not the rows being updated.

If the source join returns any rows, this can update every delivery, using an unpredictable source value when multiple rows match. The unintended Cartesian join could also explain the performance problem.

Try:

UPDATE client_delivery AS d
SET route_id = c.route_id
FROM clients AS c
WHERE d.client_id = c.id
  AND d.route_id = 0;

Assuming clients.id is unique, this updates only the intended deliveries.

6

u/gevorgter 9d ago

Thanks, you got it right. Rewrote my query as you suggested and it worked (comparable to mssql now).

And I thought i know sql pretty well :)

13

u/elevarq 9d ago

The problem with SQL is that the syntax for UPDATE and DELETE differs by database engine when using more than one table. To my knowledge, there is no ISO standard for this, so each vendor implemented it however they liked.

4

u/marr75 8d ago

Not that an ISO standard would have prevented most vendors from implementing it however they liked anyway ;)

2

u/raks1991 3d ago

One small addition to the fixed query: AND d.route_id IS DISTINCT FROM c.route_id avoids updating rows where both values are already 0. Postgres otherwise creates a new row version even though the value hasn't changed. Could save some unnecessary writes on a million-row table.

10

u/Kazcandra 9d ago

Without knowing more, you may have a missing index on a referencing table's FK. That'd be my first guess.

4

u/Accomplished-Leek176 9d ago

Indexing and probably even more important, have you done a VACUUM ANALYZE yet?

2

u/elevarq 9d ago edited 9d ago

Why VACUUM ANALYZE? That solves a different problem, when autovacuum has not been configured for your workload.

And it doesn't solve the real problem: an UPDATE that is just wrong.

3

u/Accomplished-Leek176 9d ago

If you have a large table and don’t generate stats, the query planner just does a full scan every time. You can run ANALYZE independently, but I just always run it with a manual vacuum, two birds one statement.

1

u/elevarq 9d ago

And that can only happen when you turn OFF autovacuum... That's like driving your car into a tree and then complaining about the scratches.

If you need a manual VACUUM, something is wrong.

1

u/AutoModerator 9d ago

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/cthart 9d ago

We can't help you without table definitions, including indexes.

0

u/lovejo1 9d ago

try running create index on client_delivary(client_id, route_id)

Also, if the table has any kind of locks right now, it could cause a pause until whatever has them locked has committed or rolled back.

8

u/lrweck 9d ago

Can you provide execution plans for postgres? Run a simple explain. Also, are there triggers?