News for hackers. Stuff that matters.
Re:Everybody thinks SQL joins are slow-is it because MySQL d... by PaulHoule on Friday June 17, 2011 @01:45PM (#2666400)
I definitely don't have trouble finding queries that are 50x slower on PgSQL than MySQL so I don't think it's as simple as saying one is more mature than the other in all ways.

That said, if you're doing N-way joins with N big you probably want bitmap index support too, which is another thing missing in MySQL, although I've written libraries that, for certain specialized cases, can fake it.

Re:Everybody thinks SQL joins are slow-is it because MySQL d... by kwis on Friday June 17, 2011 @02:40PM (#2666582)
Can you provide me with one or two examples that are 50x slower on pg than my? We're a postgres-heavy shop, and would be interested in finding solutions to any such problems that you can identify.
Re:Everybody thinks SQL joins are slow-is it because MySQL d... by jpeterson on Friday June 17, 2011 @01:52PM (#2666422)
> I definitely don't have trouble finding queries that are 50x slower on PgSQL than MySQL

I would love to see an example of this, given the same data, schema, similar tuning params, etc.

Re:Everybody thinks SQL joins are slow-is it because MySQL d... by lysol on Friday June 17, 2011 @02:06PM (#2666471)
Agreed. It wouldn't be very hard to find a query on a tuned MySQL box that outperforms Postgres on an untuned system. Postgres's base configuration has always been on the conservative side of tuning.
Re:Everybody thinks SQL joins are slow-is it because MySQL d... by gtuhl on Friday June 17, 2011 @04:18PM (#2666907)
Postgres has bitmap index support baked in as well and it works really well. I'd be awfully interested in knowing where MySQL beats it by 50x at anything.
Re:Everybody thinks SQL joins are slow-is it because MySQL d... by spudlyo on Friday June 17, 2011 @05:53PM (#2667231)
Some MySQL vs PG tests done by Domas (of Facebook & Wikipedia fame) show some interesting differences between MySQL and PG for an in-memory read only test. I don't think there are any 50x differences though.

http://dom.as/2010/11/08/random-poking/

Re:Everybody thinks SQL joins are slow-is it because MySQL d... by gtuhl on Friday June 17, 2011 @08:54PM (#2667628)
The tough thing about comparing these two is that it is hard to find a benchmark (I don't know of one) that works similarly with them both. Sysbench is somewhat of a standard for testing MySQL configs but Postgres does not handle it well.

Though I tend to prefer Postgres don't get me wrong - MySQL has some advantages.

For raw pkey lookups, especially range selects against pkeys with lower amounts of concurrency InnoDB's speed is unmatched. It should be given that it is completely laid out on disk specifically for that to the detriment of other features.

MySQL's replication is extremely sturdy. On paper the older method (statement-based) sounds incredibly fragile but in practice it works extremely well and has been proven on countless projects. mmm makes it almost too easy to setup replication and failover.

I feel that MySQL stalled out for several years in the 5.0-5.1 period where poorly engineered features were bolted on to create a product with too many pathological cases that destroyed performance to keep track of. All these features were available but experience taught you to avoid most joins, avoid most subselects, avoid most usages of views, etc.

That said, v5.5 has a lot of nice improvements and can actually scale up to more than 8 cores (I've gotten linear improvement up to about 32 cores and that is what Oracle puts in their white papers as well). Percona and Facebook are releasing nice patches and branches, and forks like Drizzle are reaching GA and doing good things as well. So I think it is headed in the right direction again.

Re:Everybody thinks SQL joins are slow-is it because MySQL d... by johnzabroski on Friday June 17, 2011 @04:27PM (#2666948)
Aggregates that have to do full table scans are notoriously slow in Postgres due to how multi-version concurrency control is implemented.

Any time you comment out a WHERE clause on a big table while performing an aggregate report, Postgres chokes.

Re:Everybody thinks SQL joins are slow-is it because MySQL d... by gtuhl on Friday June 17, 2011 @04:39PM (#2666988)
Compared to MyISAM? Sure for certain aggregate functions, but it's too easy to point out reasons for avoiding MyISAM: fragmentation, no transactions, table locks on writes, complete table rewrites to change columns or indexes, only scales up to about 8 cores.

Compared to InnoDB? You are incorrect, Postgres is faster, even for a raw count(*), even when comparing against the InnoDB plugin and not the ancient InnoDB builtin.

I have access to tuned TB+ DBs of both types and am happy to disprove any specific examples you can provide.

Re:Everybody thinks SQL joins are slow-is it because MySQL d... by johnzabroski on Friday June 17, 2011 @05:04PM (#2667068)
I just read up on this and it appears you are right, my issues are fixed in more recent versions of PostgreSQL. Apologies.
Re:Everybody thinks SQL joins are slow-is it because MySQL d... by johnzabroski on Friday June 17, 2011 @04:25PM (#2666944)
What do your libraries do to fake a bitmap index? When you say libraries, what layer of the stack are you referring to? Data access layer (e.g., via an ORM)?