4 ms·
Depends on your data. Sometimes (particularly when you have only 1-3 tables) MySQL is much faster and you have to go out of your way writing stored procedures t
by lqdc13 11y ago
Depends on your data. Sometimes (particularly when you have only 1-3 tables) MySQL is much faster and you have to go out of your way writing stored procedures to make Postgres compete.
Example:
select COUNT(1), status FROM some_table GROUP BY status ORDER BY status; -- Postgres 10x slower.
Also, mysql CLI is IMO much nicer than the postgres version. Better and faster autocompletes.
- nimrody 11y agoRegarding CLI: mycli and pgcli are both much nicer than the built in alternatives.
- Jweb_Guru 11y agoI'd like to see your full setup for your claim about 10x slower, because I suspect you're doing something wrong.
- lqdc13 11y agoSure, here's the pastebin of the postgres one: http://pastebin.com/Phc8KnYU http://pastebin.com/Phc8KnYU I thought I was doing something wrong, so I asked on SO: http://stackoverflow.com/questions/32755348/group-by-count-query-takes-a-long-time http://stackoverflow.com/questions/32755348/group-by-count-q... This is a very simple example, but I always run into major performance issues with Postgres. Some of them are my fault, from not knowing that array aggregation is a slow operation. MySQL advantages for me are that there is an actual UPSERT operation (an inferior version of which might be added to Postgres 9.5), that the CLI is miles ahead, and that it's much faster for a small number of tables with a lot of data and with non-complex operations.
- Jweb_Guru 11y agoI'm going to ignore your baseless (and incorrect) assertion about "an inferior version" of UPSERT (MySQL's doesn't work properly except for the primary key). Your problem is that you are not doing an index only scan. The reason you are not doing an index only scan is, most likely, that the visibility bit is not set on the table. This is usually as a result of excessive writing to the table, but sometimes it is due to incorrectly tuned planner settings. Try setting your cpu_tuple_cost higher and/or cpu_index_tuple_cost lower. I have had to do this in the past to get index only scans to work. BTW, a quick way to force the issue is to SET enable_seq_scan TO off in psql. I would add, you should really upgrade to 9.4, as there were some performance improvements to aggregates.
- lqdc13 11y agoThanks for the tips. I am not sure in which cases the MySQL one doesn't work correctly. ON DUPLICATE KEY UPDATE checks every unique fields in table, not just PRIMARY key. Also, I'll try these suggestions, but you can see that it can be frustrating that simple queries don't work quickly or randomly work really slowly and you have to find some configuration settings to hand tune.