Assaf, do you have a specific reason to use an arbitrary snapshot of the instable development branch of MonetDB? If not, I'd strongly recommend to use the latest Jun2012-SP2 release, respectively the Jun2012 release branch. We'd be curious to learn whether the latest release work better for your data & queries than the development branch. Best, Stefan Assaf Gordon wrote: Hello Jennie, Ying Zhang wrote, On 10/16/2012 04:46 PM:

How many columns and rows does you table have? How many rows does the first query return? what are the execution times of this query on mysql, postgresql and monetdb, respectively?

Below are some test results. Both servers (postgres and mserver) and clients (psql and mclient) are running on the same machine, with 8 cores and 16GB of RAM. The machine was not overloaded during the tests. The databases are stored on the same disk. I've run each test (except 5) couple of times, and listed most common result (so it's not about caching). Using PostgreSQL 9.2.1 (compiled from source) and MonetDB v11.14.0/unreleased (compiled from source). I can provide more technical details if needed. Assuming the "user" time is the time "wasted" by the client program (psql/mclient), and assuming "sys" time is negligible, then subtracting the "user" time from the "real" time gives an indication of how much time the server took to complete the query and transfer the results. It seems in most of those queries, the MonetDB server takes longer than PostgreSQL to complete. One striking difference is test #6 that needs to return just one line: the execution time for MonetDB is 9 seconds - I guess accessing 138 columns takes its toll... ## ## First table (bc251): 1,657,032 rows, 13 columns ## ## MonetDB Table size sql>select "schema", "table", sum("size") as size from storage() group by "schema","table" having "table" = 'bc251' ; +--------+-------+-----------+ | schema | table | size | +========+=======+===========+ | sys | bc251 | 132775936 | +--------+-------+-----------+ ## PostgreSQL Table Sql test=# SELECT pg_size_pretty(pg_relation_size('bc251')); pg_size_pretty ---------------- 212 MB (1 row) ## Test 1: select ONE column ## MonethDB $ time mclient -d voc -s "select seq from bc251;" > /dev/null real 0m15.981s user 0m5.653s sys 0m0.106s ## PostgreSQL $ time echo "select seq from bc251" | psql test > /dev/null real 0m3.565s user 0m2.698s sys 0m0.154s ## Test 2: select ALL columns ## MonetDB $ time mclient -d voc -s "select * from bc251;" > /dev/null real 2m16.388s user 0m38.468s sys 0m0.448s ## PostgreSQL $ time echo "select * from bc251" | psql test > /dev/null real 0m21.771s user 0m17.045s sys 0m0.843s ## Test 3: Select ONE row ## MonetDB $ time mclient -d voc -s "select * from bc251 limit 1" > /dev/null real 0m0.053s user 0m0.003s sys 0m0.004s ## PostgreSQL $ time echo "select * from bc251 limit 1" | psql test > /dev/null real 0m0.072s user 0m0.013s sys 0m0.009s ## ## Second table (NR102): 3,042,983 rows, 138 columns ## ## MonetDB Table Size sql>select "schema", "table", sum("size") as size from storage() group by "schema","table" having "table" = 'nr102' ; +--------+-------+------------+ | schema | table | size | +========+=======+============+ | sys | nr102 | 1408630784 | +--------+-------+------------+ 1 tuple (5.567ms) ## PostgreSQL Table Size test=# SELECT pg_size_pretty(pg_relation_size('NR102')); pg_size_pretty ---------------- 1850 MB (1 row) ## Test 4: Select ONE column ## MonetDB $ time mclient -d voc -s "select seq from NR102;" > /dev/null real 0m29.882s user 0m7.574s sys 0m0.107s ## PostgreSQL $ time echo "select seq from NR102" | psql test > /dev/null real 0m6.925s user 0m4.906s sys 0m0.268s ## Test 5: Select ALL columns ## MonetDB $ time mclient -d voc -s "select * from NR102;" > /dev/null real 39m58.618s user 10m9.066s sys 0m4.468s ## PostgreSQL $ time echo "select * from NR102" | psql test > /dev/null real 4m0.840s user 3m20.866s sys 0m12.592s ## Test 6: Select ONE row ## MonetDB $ time mclient -d voc -s "select * from NR102 limit 1" > /dev/null real 0m9.055s user 0m0.004s sys 0m0.008s ## PostgreSQL $ time echo "select * from NR102 limit 1" | psql test > /dev/null real 0m0.160s user 0m0.016s sys 0m0.006s _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list