big stone, What are the same results using RTree? (Also feel free to add -O2.)
? Thanks 2014-03-02 17:25 GMT+01:00 big stone <stonebi...@gmail.com>: > Hi again, > > This is what I mean : we should have an updated "speed" page where we could > objectively measure. > > In the mean time, I painfully partially reproduced two of the figures from > Wes. > > Procedure : > > download > ftp://ftp.fec.gov/FEC/Presidential_Map/2012/P00000001/P00000001-ALL.zip > unzip to P00000001-ALL.csv > > This data file is about 965 Mo, 18 columns * 5 344 498 records big. > > > ** Test Preparation ** > - Hardware : pc windows7, 4go ram, cpu intel i3-350m 2.27 Ghz > - Software : > . sqlite-shell-win32-x86-3080300 (sqlite3.8.3) > . postgresql 9.3.2.3 64bit > > - preparation scripts of sqlite (As there is an added coma at the end of > each line, The default Sqlite importation by reading headers will complain > a little) > .header on > .mod csv > .separator "," > .import "P00000001-ALL.csv" fec > > - preparation scripts of postgresql > create table fec( > CMTE_ID varchar,CAND_ID varchar,CAND_NM varchar,CONTBR_NM varchar, > CONTBR_CITY varchar,CONTBR_ST varchar, CONTBR_ZIP varchar, > CONTBR_EMPLOYER varchar,CONTBR_OCCUPATION varchar,CONTB_RECEIPT_AMT double > precision, > CONTB_RECEIPT_DT varchar,RECEIPT_DESC varchar,MEMO_CD varchar, > MEMO_TEXT varchar,FORM_TP varchar,FILE_NUM double precision, > TRAN_ID varchar,ELECTION_TP varchar,USELESS_COLUMN varchar > ); > > copy fec from 'C:\\Users\Public\\Documents\\p1all.csv' CSV HEADER; -- load > in 82 seconds > > ** Speed Tests ** > test1 = select cand_nm, sum(contb_receipt_amt) as total from fec group by > cand_nm; > ==> SQlite 21 seconds (wes = 72s) > ==> Postgresql 4.8 seconds stable (44 seconds first time ?) (wes =4.7) > > test2 = select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec > group by cand_nm, contbr_st; > select cand_nm, contbr_st, sum(contb_receipt_amt) as total from fec group > by cand_nm, contbr_st; > ==> SQlite 27 seconds > ==> Postgresql 5.7 seconds (wes=5.96) > > ** Conclusion ** > WesMcKinney "Sqlite/speed.htm" page about SQLite is 3.4 times more awfull > than what I measure. > Sqlite3.8.3 is about 4 times slower than Postgresql on this two 'raw' Data > analysis Tests. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
