• First, we had a cartesian product in the dependency view for omni_schema.dependency. We were joining operator namespace (ns) with type namespace instead of joining type namespace table (lns) with type’s namespace field. The correct, sound result should have been 32 rows. But it was doing 32 x 22 x 22 resulting in extra, completely not needed and not sound 15,488 rows which resulted in 94.9 more ms!
• We were doing materialization early but was also doing zero filtering for the table. Postgres usually handles this if it can but here, it couldn’t. Added early filtering with deptype != 'i'
• Eliminated the need for full composite type, this also boosted our speed since we didn’t need the full type. We only needed select id, d.refclassid, d.refobjid, d.refobjsubid fields.
• For acl, it didn’t have low hanging fruit like these. But introducing pre-computation to it also made a x2.7 speed boost because the actual speed blockage was from calling the entire acl view for every function, every time and trying to compute from the full view for every function. Now we compute once, then re-use it for every function that needs it.
• omni_schema.dependency also got a x2.1 speed boost. From around 266 ms to 128 ms.
• Using BUFFER and EXPLAIN together is a bliss.

/claim #823

Would love to hear your feedback on this @yrashk