Look ma, I wrote a new JIT compiler for PostgreSQL

Re:Look ma, I wrote a new JIT compiler for PostgreSQL by aeyes on Monday March 18, 2024 @10:42AM (#39745232)

The plan cache on Oracle in combination with prepared statements where the optimizer can't peek into the parameters has been really problematic for me in the past. I usually had to go in and either add hints or force a plan.

Even simple queries like SELECT * FROM t WHERE x = TRUE; could turn into a nightmare depending on the distribution of the x values in the table.

With Postgres I rarely encountered such problems but I must admit that I haven't used Postgres with prepared statements.

I have seen some queries with slow planning time (>100ms) where a cache could have been useful but I don't remember ever really needing to optimize one.

[ Parent | on HN ]

Re:Look ma, I wrote a new JIT compiler for PostgreSQL by SigmundA on Monday March 18, 2024 @11:18AM (#39745726)

Is x a parameter because it doesn't look like it? MSSQL has parameter sniffing and will make multiple plans based on incoming parameters I would be surprised if Oracle does not do the same. It can actually be problematic to sniff parameters sometimes and it can be disabled with a hint!

[ Parent | on HN ]

Re:Look ma, I wrote a new JIT compiler for PostgreSQL by aeyes on Monday March 18, 2024 @11:41AM (#39746041)

I just made up something simple but yes, I had these problems with bind variables in prepared statements.

> SELECT * FROM t WHERE x = :var;

But I haven't used Oracle in years and back in the day, there was no bind variable peeking.

[ Parent | on HN ]