Re:For Want of a JOIN
by rubyist5eva on Thursday December 22, 2022 @01:13PM
(#34096471)
Agreed, partial indexes were a game changer for me. After that, Window Functions was like a whole new world of awesomeness opened to me. Nothing has been the same ever since I figured out how to effectively use Window Functions. One of the biggest Eureka! moments of my entire career.
[ Parent
| on HN ]
Re:For Want of a JOIN
by giovannibonetti on Thursday December 22, 2022 @05:02PM
(#34099432)
Actually I was talking about covering indexes:
CREATE INDEX ... INCLUDE ...
They can be used to speed up queries that have WHERE clauses, so I see it might have caused some confusion since partial indexes have WHERE clauses in the their definition.
Re:For Want of a JOIN
by rubyist5eva on Friday December 23, 2022 @03:20PM
(#34110176)
Interesting, I've never seen INCLUDE before - probably because we use a version of postgres that does not support it - but I see now that it can be useful instead of doing CREATE INDEX idx ON table (colA, colB) because of the lack of ordering in the b-tree leaves. Good stuff. Just another piece of ammunition for upgrading our db to a newer version that has all sorts of new goodies I've been reading about lately.