Hi Navin, Excuse me if some of the points below repeat things you already know.
1. Dr. Hipp's advice not to create redundant indexes was *not* intended to give you very quick row counts -- Simon Slavin et al had already given advice to speed up row counts -- and just now Stefen Keller even provided trigger sql statements for you. The trigger solution will result in almost instantaneous row counts (at the expense of slightly slower inserts and deletes.) 2. Alternatively, since you write that you only do deletes every 5 days, then if by chance the time for running VACUUM just after these periodic batch deletes is tolerable, then SELECT max(ROWID) FROM hp_table1; should give you instant satisfaction without using the TRIGGERs. (if you choose this solution you would NOT specify WITHOUT ROWID of course.) 3. We're all assuming that your application truly NEEDS to know the row count (and that you're not just using "select count(*) from hp_table1" as some "arbitrary test sql" for sqlite.) 4. The fact that select count(*) from very_large_table; can be slow is *not* evidence that sqlite can't handle tables as large as yours. Sqlite can update its b-trees till the cows come home. It was a design trade-off. Sqlite could have been designed to always keep up with table sizes, but inserts and deletes would be slightly slower for all tables for all users, and in a great many applications it's not needed. On the other hand, applications that *do* need quick access to the current row count can have it using triggers. Other databases might have been designed differently. (There *are* however, many applications ill-suited to sqlite -- often because of high concurrency needs or the need for fancy features, but row counting is not a veto item. https://www.sqlite.org/whentouse.html) 5. IMPORTANTLY -- I see you tried different page sizes, but did you did you also set a large CACHE size? E.g., "PRAGMA CACHE_SIZE= -1000000" would set the cache to about 1 GByte. Did I see that your postgres test was using 4GB? You'd want to compare using about the same cache I would think. (A large cache alone does NOT substitute for one of the "fast row count" solutions above, but it might make a big difference in your other operations.) https://www.sqlite.org/pragma.html#pragma_cache_size Regards, Donald Griggs _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users