davidrowley

6 ms·

1.
by davidrowley 2y ago
The Oversized-Attribute Storage Technique. https://www.postgresql.org/docs/17/storage-toast.html
2.
by davidrowley 3y ago
> 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 multiple selectivities? Looking at https:/
3.
by davidrowley 3y ago
> 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 zero correlation between columns, but what value is
4.
by davidrowley 3y ago
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 build two hash tables and only probe the 2nd one if the first lo
5.
by davidrowley 3y ago
> 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 the table IS the index and any lookup on the key has no i
6.
by davidrowley 3y ago
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 recursive CTEs comment is skip scanning using an index and loo
7.
by davidrowley 3y ago
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.
8.
by davidrowley 3y ago
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 would scale better for additional rows
9.
by davidrowley 3y ago
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 choosing the cheapest version of a subplan once the pla
10.
by davidrowley 3y ago
> 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 stable order of rows beyond what's specified in
11.
by davidrowley 3y ago
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 or are most expensive to discover when the planner &qu
12.
by davidrowley 3y ago
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 join to 1 row?! It just does not go well when 1 row turns int
13.
by davidrowley 3y ago
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 thinks the outer side of the join contains a single ro
14.
by davidrowley 3y ago
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...
15.
by davidrowley 3y ago
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 provably contains unique values. Of course, you could check f
16.
by davidrowley 3y ago
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 loops are useful to avoid sorting or hashing some large relation
17.
by davidrowley 3y ago
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 generate JITed code/LLVM-IR t
18.
by davidrowley 3y ago
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 pass, then re-run if the estimated total cost of the plan
19.
by davidrowley 3y ago
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 the plans needn't be worse than they are today.
20.
by davidrowley 3y ago
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, most of the people that have issues with slow JIT times are ha
21.
by davidrowley 3y ago
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 from a Hash or Merge join to that, then you'd likely n
22.
by davidrowley 3y ago
(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 you've implied above. It's worse because a new plan
23.
by davidrowley 3y ago
> 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 Postgres committer). I very much agree that the code to decid
24.
by davidrowley 3y ago
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.
25.
by davidrowley 3y ago
(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 that detecting when it's possible is as simple as
26.
by davidrowley 3y ago
Postgres does not currently reuse JIT-compiled code. JIT will run each execution of the query. This may change in the next few years, likely starting with tuple deforming, as that's fairly reusable, per table for any query.
27.
by davidrowley 3y ago
It sounds like it would have to be an opt-in feature which could be applied per query, as otherwise wouldn't it be equally as annoying if the planner didn't adapt to the table data changing? What may be better is if the executor p
28.
by davidrowley 3y ago
> The one I'd love to tell the planner is that a table holds transactions in time, and that it should not expect that today's data is empty because it was empty 10 hours ago. It's an extremely common pattern, it makes any
29.
by davidrowley 3y ago
It's pretty hard to tell if a plan is good or bad from EXPLAIN without using the ANALYZE option. With EXPLAIN ANALYZE you can see where the time is being spent, so can you get an idea of which part of the plan you should focus on. To
30.
by davidrowley 3y ago
> you don't want your "optimizer stats" to be as big as the whole dataset itself. So optimizer has limited information, by design, and it has to come up with _something_ in a very short amount of time. This is very true. P

More ›