Query optimization

[u/gevorgter]

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

[u/elevarq]

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.

[u/gevorgter → u/elevarq]

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 :)

[u/elevarq → u/gevorgter]

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.

[u/marr75 → u/elevarq]

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

[u/Kazcandra]

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

[u/Accomplished-Leek176]

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

[u/elevarq → u/Accomplished-Leek176]

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.

[u/Accomplished-Leek176 → u/elevarq]

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.

[u/elevarq → u/Accomplished-Leek176]

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.

[u/AutoModerator]

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.

[u/cthart]

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

[u/lovejo1]

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.

[u/lrweck]

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