On Sun, Mar 2, 2014 at 11:25 AM, big stone <stonebi...@gmail.com> wrote:
>
> ** 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)
>
>
My guess is that PG is creating the appropriate index on the first
invocation, which is why the first run on PG takes so much longer. SQLite
runs without an index in every case.
What are your performance measurements using SQLite when you create an
index appropriate for the query. An index that will be appropriate for
both the previous and the following query would be:
CREATE INDEX xyzzy ON fec(cand_nm, contbr_st);
Even better would be a covering index:
CREATE INDEX xyzzy2 ON fec(cand_nm, contbr_st, contb_receipt_amt);
What is SQLite's time after it has one or the other of the indices above?
> 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
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
