3 ms·
I've been trying that, and I just keep running into miserable query performance. What order of magnitude do you use this on and find it acceptable? 1MB, 1GB, 1
by timeinput 1y ago
I've been trying that, and I just keep running into miserable query performance.
What order of magnitude do you use this on and find it acceptable? 1MB, 1GB, 1TB, 1PB? At 1GB it seems okay. At 10GB things aren't great, at 100 GB things are pretty grim, and at 1TB I have to denormalize the database or it's all broken (practically).
I'm not a database expert, but I don't feel like I'm asking hard questions, but I'm running into trouble with something I thought was 'easy', and matches what you're describing.
- bccdee 1y agoTry using the `explain analyze` statement. My guess is, either you need to add indexes, or you're not using the indexes you do have because some of your subqueries are being materialized when they shouldn't be. I can't be sure of the specific problem, but you should be able to fix it with some run-of-the-mill query optimization.
- mycall 1y agoChained CTEs that reference previous expressions can cause the query plan to explode sometimes. My only work around for that situation is to switch to using temp tables with indexes, replacing some (or all) of the table expressions until things are fast again.
- bccdee 1y agoYeah—lasy time I had that issue, I found postgres was materializing some of the subqueries and then naively scanning those materialized queries when the original tables had indexes. I fixed it by adding "not materialized" and hitting the original tables directly, but if you want to materialize a subquery AND have it be indexed, a temp table is the only way I know to make that happen.