Re: Speed Question

Noah,

> Speed is one of our major concerns, so before switching we've decided
> to perform some speed tests.
> From what I understand, Postgresql is NOT as fast as Mysql,

This is a PR myth spread by MySQL AB. The truth is:

1) PostgreSQL, unconfigured and not optimized, is indeed slower than MySQL
out-of-the-box. MySQL is meant to be idiot-proof; PostgreSQL is not,
intentionally.

2) Nobody has yet come up with a database benchmark that both MySQL AB and the
PostgreSQL team are willing to accept; depending on whose benchmark you use,
either could be faster -- and neither benchmark may approximate your setup.

> We've installed the software and have run some basic insert, index and
> query tests that
> seem ridiculously slow. I can't help thinking that we are doing
> something wrong, or
> don't have things configured for optimal performance.

Almost undoubtedly. Have you modified the postgresql.conf file at all?
Where are your database files located on disk? How are you construting your
queries?

> We've performed these same tests on Mysql and then run dramatically
> faster.

Without transations? Sure. Turn off transaction logging, and PostgreSQL
runs faster, too.

>
> Here's the initial performance test results and issues...
>
> Table configuration:
> speedtest( prop1 integer, prop2 integer, prop3 integer, prop4 integer);
> indexes on each of the four individual property fields
>
> Each record consists of four random integers, uniformly distributed,
> between 0 and 1000. The integers are computed in the perl script
> used to populate the table, not using an SQL random() function.
>
> Hardware configuration: P3-500, 384MB ram, *unloaded* system.
> Software configuration: Linux 2.4.20, reiserfs, standard slackware
> install.

You haven't mentioned your PostgreSQL memory settings, by which I assume that
you haven't configured them. This is very important.

> Issue #1: Speed of inserts is relatively slow. 100000 inserts is
> taking
> roughly 10 minutes. This isn't EVIL, but mysql appears to be about
> ten times faster here. Is there something we could do to the indexes
> differently? Disable transactions? Is there a more "raw" insert, which
> may not set off triggers?

Bundle them in a single transaction. Move pg_xlog to a seperate drive from
the database.

> Issue #2: It doesn't appear as though multiple indexes are being used.
> ie: select count(*) from speedtest where (prop1 between 100 and 200)
> and( prop2 between 100 and 200) and (prop3 between 100 and 200)
> and (prop4 between 100 and 200) formulates a query plan that only
> uses one index. The following is pasted from the 'explain select' ---

That's correct; Postgres will only use a single index on this query. If you
want to reference all columns, create a multi-column index. Note that,
however, Postgres is likely to reject the index as it is just as large as the
table. In this way, your test is insufficiently like real data.

Good luck. Why not use the Open Database Benchmark for testing, instead of
inventing your own?

http://www.sf.net/projects/osdb

--
-Josh Berkus
Aglio Database Solutions
San Francisco