11 ms·
The distinct on the date format is the problem, probably should use a separate query on each table to fill in the date output as a new column first, and then jo
by manigandham 8y ago
The distinct on the date format is the problem, probably should use a separate query on each table to fill in the date output as a new column first, and then join afterwards.
Also wrong tool for the job comes to mind. MariaDB/MySQL are just not great at joins and query planning, and at this scale, especially with all the work involved to export and use unix tools, why not use any of the columnar data warehouses that can handle this much faster?
Export it to bigquery with CSVs and the query will probably finish in 30 seconds.
- brightball 8y agoOn PostgreSQL I've also had really positive experience with the cstore_fdw from Citus. It's an extension you have to compile yourself but you end up with the ability to create compressed columnar tables. Trimmed some heavy queries that I was working with from about 4 hours to about 3 minutes. Also compressed the data from 220gb to about 20gb.
- manigandham 8y agoYes, 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 yet (and is focused on an in-memory engine first).
- massaman_yams 8y agoAnd 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 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-and-amazon-rds-postgresql-with-dblink/ https://aws.amazon.com/blogs/big-data/join-amazon-redshift-a...
- manigandham 8y agoWell yes, like I said it's not really a true columnstore. It's a basic storage extension that writes table data as ORC files for good compression and in the best-case scenario can managed to filter out segments. It's missing all the fancy processing features that real columnar warehouses use so it'll never be as fast.
- chucky_z 8y agoThis is also an older benchmark. I'd be curious to see this used against postgresql 11; there have been massive speed increases in 10 and now 11. 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.
- brightball 8y agoThis was doing some analysis on my laptop. What other free columnar options are out there for Postgres? For the specific dataset I needed to take advantage of some of the additional datatypes that PG has available, which is how I found it.
- sigi45 8y agoI also run often enough in bad execution time on MySQL for queries which looked really simple to optimize. The rewrite was easy and performance went through the roof.
- olavgg 8y agoI highly recommend Clickhouse for this. It is blazing fast and can do over 100GB/s on a single modern machine if you have enough RAM. And it is very easy to install and configure.
- manigandham 8y ago> And it is very easy to install and configure On a single node yea, unfortunately not beyond that. Really wish there was bigger focus on operational features, and a bigger community for a rather fantastic product.
- RhodesianHunter 8y agoThis was my exact reaction to the OP's comment as well. CH is an amazing piece of tech, but getting configuration just right on many nodes is quite complex, and all of the experts writing about how to do so are writing in Russian. The documentation seems to be improving rapidly though.
- lykr0n 8y ago+1 for ClickHouse. But it's the kind of software you need to figure out before you scale beyond a single node. Fast as hell and surprisingly performance in low memory conditions.
- deleted 8y ago[deleted]