Viewing profile — davidrowley

The Oversized-Attribute Storage Technique. https://www.postgresql.org/docs/17/storage-toast.html

> Multi-selectivities are good, but IIRC they can't be specified across tables and thus across joins, right? Yeah, no extended statistics for join quals yet. > how do you reconcile…

> I mean, what we have right now (multiply selectivities together as if they were independent) is also pretty dumb Yeah, I think it was probably a mistake to always assume there's …

UNION is certainly one way to eliminate the OR condition. In theory, a hash join is possible with a condition like `ON t1.a = t2.a OR t1.b = t2.b`, but Hash Join would need to buil…

> Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL, usually your primary key is also set as the clustered index so that …

SELECT DISTINCT has seen quite a bit of work over the past few years. As of PG15, SELECT DISTINCT can use parallel query. I imagine that might help for big tables. I assume the rec…

Was there an indication of the number of functions compiled? There is work ongoing in this area, so feedback on this topic is very welcome on the PostgreSQL mailing lists.

Yeah, this is similar to some semi-baked ideas I was talking about in https://www.postgresql.org/message-id/CAApHDvo2sMPF9m=i+YPPU... I think it should always be clear which open w…

Yes, I think so too. There is some element of this idea in the current version of PostgreSQL. However, it does not go as far as deferring the decision until execution. It's for cho…

> If the sort order isn't fully determined by a query, can the query plan influence the result order? Yes. When running a query, PostgreSQL won't make any effort to provide a stabl…

I think the join search would remain at the same level of exhaustiveness for all levels of optimisation. I imagined we'd maybe want to disable optimisations that apply more rarely …

I agree. The primary area where bad estimates bite us is estimating some path will return 1 row. When we join that Nested Loop looks like a great option. What could be faster to jo…

I think the first step to making improvements in this area is to have the planner err on the side of caution more often. Today it's quite happy to join using a Nested Loop when it …

It's a bit complex to explain here, but I describe an idea I've been considering in https://www.postgresql.org/message-id/CAApHDvo2sMPF9m=i+YPPU...

You have to remember that because the query has an ORDER BY, it does not mean the rows come out in a deterministic order. There'd need to be at least an ORDER BY column that provab…

That could be useful if there was a way to just disable non-parameterized nested loop, however enable_nestloop=0 also disables parameterized nested loops. Parameterized nested loop…

There's some information about why that does not happen in https://www.postgresql.org/message-id/20211104234742.ao2qzqf... In particular: > The immediate goal is to be able to gene…

I've considered things like this before but not had time to take it much beyond that. The idea was that the planner could run with all expensive optimisations disabled on first pas…

You'd still need analyze to gather table statistics to have the planner produce plans prior to getting any feedback from the executor. So, before getting feedback, the quality of t…

Perhaps, but it might be harsh to say it was the wrong decision when it was made as partitioned tables are far more optimised than when JIT was first worked on. It seems to me, mos…

There certainly are valid reasons for this. For example, adding a join condition with an OR clause. The only join operator that supports non-equi joins is Nested Loop. If you went …

(blog author and Postgres committer here) I personally think this would be nice to have. However, I think the part about sending of tuples to the client is even more tricky than yo…

> One part of this I found rather frustrating is the JIT in newer Postgres versions. The heuristics on when to use appear not robust at all to me. (Author of the blog here and Post…

Just to clarify. I'm the author of the blog. I work for Microsoft in the Postgres open-source team. All the work mentioned in the blog is in PostgreSQL 16, which is open-source.

(Author of the blog and that feature here) This one did crop up on the pgsql-hackers mailing list. I very much agree that it's unlikely to apply very often, but the good thing was …