davidrowley

> At least Oracle folks had enough at some point and introduced an "optimizer_ignore_hints" parameter [1], so all legacy hints that were added 20 years ago just get ignored - and the modern optimizer does a much better job getting things right.

I think the general attitude in the Postgres community is been from a purist point of view. When you have a codebase around 40 years old, you do have to think carefully about what you put into it, as it might not be that easy to take it out again. However, yes, I do think hints would be useful for Postgres, providing they're done well. It would be good to at least have something to assist with selectivity estimations. Those are at least not directly forcing the planner into a single choice. New planner/executor smarts, such as something like Memoize added in PG14 could still be considered after upgrading an older pre-PG14 application with such hints, but perhaps not if the hint told the planner that it must nested loop join these two tables.

(Postgres committer and blog author here) Personally, I don't have any objection to hints. The resolution of any statistics is never going to be high enough to always be accurate enough for all cases. I think it would be good to give DBAs a better way to coax the planner into making or not making a certain decision. It would also be nice if the planner was a little more risk-averse. Currently, it's happy to do things like Nested Loop join because it thinks some complex WHERE clause will only match 1 row. Nested Loop works best for that, but if there are 2 rows, then generally, any other join type is better, especially so when the inner side of the join is expensive.

There is a pg_hint_plan extension. I think the danger with hints is that they might only be correct when written. If the table sizes or data skew changes, they might make things worse. I don't have a link to hand, but last time I recall a discussion on hints there was no general objection to them, providing the implementation could be done in a way that didn't force the planner's hand too strongly and still allowed it to adapt to the underlying data changing. For example, indicating there's a correlation between two columns, rather than specifying a given predicate matches 10 rows.

(blog author here) Yes, thanks for raising this. I didn't manage to complete my analysis for the regression at 64MB work_mem before publishing the blog. I drew up my analysis on https://www.postgresql.org/message-id/CAApHDvqXpLzav6dUeR5vO... shortly after publishing. Basically it relates to the generation memory context having a slightly larger chunk header than the original aset context combined with the tuple width of my test table being exactly 64 bytes, which results in no power-of-2 rounding savings. This causes slightly fewer tuples to be stored per batch in PG15, which results in more batches (tapes) and slower performance. If I'd added another column to that table the results would have been very different. PG14 would have rounded the allocation size up to 128 bytes and only about half the tuples would have been sorted per tape. The allocation size would be 72-bytes in PG15 with an extra column in the test table.

If I remember correctly, SQL Server will convert NOT IN to anti-join. PostgreSQL currently does not do that due to NOT IN being incompatible with anti-joins in regards to NULL values. There's room for improvement there by detecting if NULLs can exist or not, and converting if they can't.

If you don't need the NOT IN weirdness around NULL values then I'd suggest you just use a NOT EXISTS. That'll allow something more efficient like a Hash Anti Join to be used during the DELETE. Something like:

Delete From "APCRoleTableColumn" Where Not EXISTS (Select 1 From information_schema.columns SC INNER JOIN "APCRoleTable" RT ON SC.table_name = RT."TableName" Where RT."TableName" = "APCRoleTableColumn"."TableName" AND SC.column_name = "APCRoleTableColumn"."ColumnName" AND SC.table_schema = 'public');

Is that faster now?

The visibility map is just 1 bit per page. Vacuum sets these bits to "1" when it sees that all tuples on the page are visible to all transactions. i.e. all tuple xmins are <= the oldest running transaction and none of the tuples have not been marked as deleted by any transaction yet. The visibility map bit will be unset when a new tuple is added to the page or an existing one is "deleted" or more accurately, has the xmax set with the deleting transaction's ID.

The visibility map is stored on-disk as a different fork of the filenode for the table. Two bits are actually stored per page, 1 for visibility and another to mark if the page only contains only frozen tuples. The frozen bit helps reduce the cost of vacuuming the table for transaction wraparound, which is also mentioned in the blog post.

The query planner does not count these bits to determine if it should perform an Index Only Scan vs an Index Scan. An approximate value is stored in pg_class.relallvisible.

Index Only Scans are a thing in PostgreSQL, however, they may still need to visit the heap if the visibility map bit for the heap page indicates that the not all tuples on the heap page are visible to all transactions. When a high percentage of pages are marked as "allvisible" then Index Only Scans can give a good boost to performance.