Hi Jatin,

thank you very much for your interest in MonetDB!

We are glad to hear that you found MonetDB "way faster" than PostgreSQL with bulk loading your 250 MB dataset --- btw, which versions of MonetDB and PostgreSQL are you comparing on what kind of system (OS, hardware (CPU, RAM, I/O system))?

With a simple select * from tablename, you basically evaluate to ability of both server and client to pump your 250 MB of data from the server to the client. That is probably an interesting measurement in case you mostly do select * from table queries. However, we do not consider such queries the "major challenges" for DBMSs and designed MonetDB not for plainly pumping all data from the server to the client in the most efficient way. Rather, we assume that the major part of data-management, i.e., processing (more) complex queries including selections, joins, aggregation, etc. are done by the DBMSs (server) and only the rather small results of such (analytical) queries are then sent to the server. Moreover, as opposed to "classical" row-stores, column-stores in general are not necessarily designed with a major focus on most efficiently re-constructing the whole table. They rather assume that most (analytical) queries usually only access a subset of all column of a table. Being able to access only the requested data and hence not having to carry-around excess-luggage of non-used columns during query processing is one of the key design differences of column-stores over row-stores.

Hence, to speed up simple select * from tablename queries in MonetDB, you'd probably (at least) need to re-design and -implement MonetDB's client-server protocol (MAPI).

We are curious, though, to also hear about your experiences with more complex queries that return much smaller (i.e., "more reasonable"?) result sets.

Stefan

ps: It is interesting to hear that the "vacuum" command in PostgreSQL had a significant impact on a simple select * from table query over bulk-loaded data, i.e., without any updates being performed --- I wouldn't know wht akind of "garbage" bulk-loading leaves behind that needs to be cleaned-up ...

On Fri, Aug 21, 2009 at 07:18:46PM +0530, jatin patni wrote:

Hi, I am in the process of evaluating two major databases....monetdb and postgresql for building a backend database system for backtesting on huge historical data sets around 100GB. I copied some test data(a 250mb table) using bulk copy in both and monetdb was way faster. But when I did a simple query "SELECT * from tablename", it took around 3 minutes for 1 lakh rows via mclient and a gui interface (aqua studio based on JVM simply said out of memory). The bulk copy file was in the format collumns seperated by pipes in a text file. While using postgresql, at first it was slow but once I did a vacuum(function in postgresql), the (select * from tablename) was much faster than monetdb.

So, what am I missing here, Do I need to do something to enhance the performance in Monetdb....... I have high hopes from monetdb, so please help. Thanks

-- Jatin Patni Tel: 91 9911 649 657 jatinpatni@gmail.com www.jatinpatni.co.nr

---

Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

On Sat, Aug 22, 2009 at 10:47:11PM +0200, Fabian Groffen wrote:

On 22-08-2009 09:57:22 +0200, Stefan Manegold wrote:

...

ps: It is interesting to hear that the "vacuum" command in PostgreSQL had a significant impact on a simple select * from table query over bulk-loaded data, i.e., without any updates being performed --- I wouldn't know wht akind of "garbage" bulk-loading leaves behind that needs to be cleaned-up ...

If I recall correctly, vacuum also calls analyze implicitly, which means statistics are being built, and hence a simple select * from X, can be answered from the statistics without even looking at table X itself.

it's a "select * from X", not a "select count(*) form X"; hence, the statistics can IMHO only answer that query, if the statistics (also) contain all the data of table X ... ;-)

Stefan