For Want of a JOIN

Re:For Want of a JOIN by boredemployee on Thursday December 22, 2022 @12:01PM (#34095558)

Not related, but here in my job our query just stopped working because the data got too big. we use left joins everywhere because we don't want to lose the main table data. do you think the trick you just mentioned could optimize our left join as well?

[ Parent | on HN ]

Re:For Want of a JOIN by giovannibonetti on Thursday December 22, 2022 @05:00PM (#34099399)

I suppose so. I would just check if these tables are vacuumed often (preferably with autovacuum) otherwise the query planner might decide not to use the index depending on the visibility map conditions.

This post explains it in more detail: https://blog.pythian.com/postgres-covering-indexes-and-the-v...

[ Parent | on HN ]

Re:For Want of a JOIN by Corazoor on Thursday December 22, 2022 @05:54PM (#34100033)

Most likely, yes. Combined indexes are real cool, because if the database can satisfy a join only from the index, it can usually perform it completely in memory, instead of having to do a table scan/hitting the disk for the values.

Another trick people usually shy away from: temporary tables. It might seem slow and wasteful to create a table (plus indices) just to store results for a fraction of a second, but for very large tables with large indices, creating a smaller index from the baseteable and joining against that can be magnitudes faster!

There is even dedicated syntax for that: CREATE TEMPORARY TABLE. They are local to the connection and will get dropped automatically at the end of the sql session.

They are also great for storing the results of (nondependent) subqueries, because for large sets, not every database is able to find the proper optimizations. Mysql versions < 8 for example.

I really recommend you to try that one. So far I could fix every "query takes too long" problem that resisted other solutions that way.

[ Parent | on HN ]