News for hackers. Stuff that matters.

A comment on What's new in the Postgres 16 query planner (a.k.a. optimizer). Parent comment

Re:What's new in the Postgres 16 query planner (a.k.a. optim... by SigmundA on Friday February 09, 2024 @09:51AM (#39315353)
Unlike say MSSQL or Oracle PG does not cache plans at all. I think this is mostly due to its multiprocess architecture vs just sharing in memory plans between threads. In MSSQL a plan can take a while to optimize including jitting if needed but it doesn't matter that much because all plans are cached so when that statement comes in again the plan is ready to go.
[ Parent | on HN ]
Re:What's new in the Postgres 16 query planner (a.k.a. optim... by nextaccountic on Friday February 09, 2024 @10:49AM (#39315998)
> I think this is mostly due to its multiprocess architecture vs just sharing in memory plans between threads

You can share stuff with a multiprocess architecture just fine (either through IPC or just plain shared memory + synchronization)

It's true that threads share memory by default, but processes can opt into sharing memory if they wish. And it appears that Postgres already makes use of shared memory for some things

https://www.instaclustr.com/blog/postgresql-docker-and-share...

https://stackoverflow.com/questions/32930787/understanding-p...

(random links from Google just to illustrate the point)

[ Parent | on HN ]
Re:What's new in the Postgres 16 query planner (a.k.a. optim... by SigmundA on Friday February 09, 2024 @12:44PM (#39317834)
PG shares data between process not code as far as I know and definetly not any plans. Sharing jitted code is not straight forward as pointers will be different per process.
[ Parent | on HN ]
Re:What's new in the Postgres 16 query planner (a.k.a. optim... by fakwandi_priv on Friday February 09, 2024 @11:28AM (#39316518)
https://www.postgresql.org/docs/current/plpgsql-implementati...

Seems like these will get cached at some point.

[ Parent | on HN ]
Re:What's new in the Postgres 16 query planner (a.k.a. optim... by SigmundA on Friday February 09, 2024 @12:39PM (#39317767)
Only within a session unless something has changed there, that means no sharing between clients or even from one connection to the next form the same client.

MSSQL caches plans globally and can be used across sessions and connections if the statement text is the same.

[ Parent | on HN ]