81–90 of 91 posts
I don't know mysql/mariaDB that well, but I know in ms sql server that doing a DISTINCT on a Function([Column]) would not use an index on [Column]. In this case it would be much faster to have an intermediate table or column with that statistic that could then be properly indexed.
Earlier quoted context omitted.
It's also a negative side-effect of the improvement of software and automation in this space. In ye olden times, there would be an grumpy DBA in this story terminating the query and yelling at the user. Many, many people are consuming databases without understanding how to approach SQL optimization, because nobody is forcing them to!
There's a happy middle ground between what we have now and those DBAs playing hardass without even trying to understand the need.
Although the parent posed a caricature with "yelling", this is an even more extreme one.
The "forcing" function merely requires a bit of "hardass" but not the willful ignorance you associate with it. At the risk of going true-scotsman, I believe that competent DBAs capable of the forcing function would already understand the need without trying.
As such, I don't think there needs to be any kind of "medium" between two extremes of ignorance, but, rather, back to the GP's point, a reduction of ignorance in "what we have now".
Of course, I don't believe hardass/yelling is the best way to achieve that, either, instead favoring (original) Devops culture, but that also doesn't work if Ops (including DBA) skills aren't recognized as valuable any more (and "Devops" just means "replace Ops with Devs").
I'd also like to see a breakdown of the time taken to complete the project. RAM is cheap and jumping from 16GB to 64GB (or even 128GB) might have cost as much as the analysis time. The only clue to that a memory upgrade might have been a quicker fix was that the merged file was 133G. Seems to me that an upgrade to 128GB of RAM might have led to a dramatically shorter query execution time.
If you need to add hardware to solve a software optimization problem such as this, you've failed your job as a software engineer (if you should even be called an engineer, neigh, a developer). I'll probably get downvoted for this, but you should should examine opportunities to optimize your SQL/other storage patterns before you assume anything else if your indicators are your DB is slow.
That's an interestingly narrow viewpoint with which I, narrowly, agree.
I suspect that the narrow viewpoint is just a form of the aphorism "if all you have is a hammer, everything looks like a nail."
> if you should even be called an engineer (if you should even be called an engineer, neigh, a developer)
However, in the broader context of engineering as problem solving (or developing a product), I disagree.
Not all (computer) problems are best solved with software.
As a sysadmin (who can code but doesn't love to), I routinely struggle with managers who don't have an intuitive sense of even a first approximation of what modern hardware is capable of, because their entire background is programming.
The fact that (latency) "numbers every programmer should know" exists (and has for quite some time) is fairly telling. What's more telling is that there are no dollar values attached to any of them.
Earlier quoted context omitted.
Yes, it's great. Part of the issue is that PostgreSQL isn't great at parallelism yet so optimizing the storage greatly reduces compute time to begin with. Unfortunately that extension has a bunch of limitations and issues that keep it from being production-ready. PostgreSQL could really use a proper columnstore table implementation, and there's a pluggable storage API on the roadmap but it hasn't gotten much traction…
And it's... really not all that fast when compared to mature analytical databases. ClickHouse on identical hardware is ~ 100x faster than cstore_fdw. http://tech.marksblogg.com/benchmarks.html More interesting to me is the reverse: using FDW from the analytical DB to Postgres, e.g., https://aws.amazon.com/blogs/big-data/join-amazon-redshift-a...
I'd also like to see a breakdown of the time taken to complete the project. RAM is cheap and jumping from 16GB to 64GB (or even 128GB) might have cost as much as the analysis time. The only clue to that a memory upgrade might have been a quicker fix was that the merged file was 133G. Seems to me that an upgrade to 128GB of RAM might have led to a dramatically shorter query execution time.
If you need to add hardware to solve a software optimization problem such as this, you've failed your job as a software engineer (if you should even be called an engineer, neigh, a developer). I'll probably get downvoted for this, but you should should examine opportunities to optimize your SQL/other storage patterns before you assume anything else if your indicators are your DB is slow.
But you've succeeded in your job in being a practical person trying to get something done.
I would love to know where you work, where "let me spend some time on optimizing this query" could be justified over "lets put a few more sticks of ram". Everywhere that I've worked, developer time is much more valuable and much more costly than a few ram sticks (assuming it's just a server or two, and not N instances).
Earlier quoted context omitted.
Yes, it's great. Part of the issue is that PostgreSQL isn't great at parallelism yet so optimizing the storage greatly reduces compute time to begin with. Unfortunately that extension has a bunch of limitations and issues that keep it from being production-ready. PostgreSQL could really use a proper columnstore table implementation, and there's a pluggable storage API on the roadmap but it hasn't gotten much traction…
And it's... really not all that fast when compared to mature analytical databases. ClickHouse on identical hardware is ~ 100x faster than cstore_fdw. http://tech.marksblogg.com/benchmarks.html More interesting to me is the reverse: using FDW from the analytical DB to Postgres, e.g., https://aws.amazon.com/blogs/big-data/join-amazon-redshift-a...
One of the most interesting things is you can shard postgres extremely easily now by using FDWs which can do native pushdown of optimizations.
Also, Citus itself has been improving in that time, and I'm curious to see how that performs.
I'd write this query:
select pc.project_id, date_format(c.created_at, '%x%v1'), count(*)
from commits c
join project_commits pc
on c.id = pc.commit_id
group by pc.project_id, date_format(c.created_at, '%x%v1')
Earlier quoted context omitted.
I guess you were missing an index, as data order should only matter when doing full scans.
this was a full self-join (SELECT * FROM blah a, JOIN blah b WHERE a.id = b.id). I ran strace on it, for each a item, it was doing a seek to each b row and reading a whole page. By pre-sorting, I increased locality.
Depending on when you originally did the query, maybe you were using 5.5 or below, which would explain why you now can’t reprodce it?
Earlier quoted context omitted.
this was a full self-join (SELECT * FROM blah a, JOIN blah b WHERE a.id = b.id). I ran strace on it, for each a item, it was doing a seek to each b row and reading a whole page. By pre-sorting, I increased locality.
MySQL only supported “nested loop” joins in 5.5 and below. Hash and merge joins were introduced in 5.6. Depending on when you originally did the query, maybe you were using 5.5 or below, which would explain why you now can’t reprodce it?
I think the date_format call forces a table scan, the query could be rewritten to be much faster. Optimizing the sql statement seems less error prone than extracting the data to text files.
There's no predicate or limit or inner join that could reduce the set of rows, so the table might as well be scanned.