Thanks for the replies. - All insertions are within a transaction - Database was originally created on Linux (with a 1K page size) and copied to Windows - Changing the page size to 4K (and vacuuming) lowered the index creation time on Windows to 50 seconds and on Linux to 5.5 minutes. However, there is still a huge disparity. - Both Windows and Linux are doing nothing else and have ample memory. The disk on Linux is a 10000 rpm fast SCSI disk on an HP blade.
Here is the Windows output of the analyzer for the table and its index: *** Table XXX w/o any indices **************************** Percentage of total database.......... 8.1% Number of entries..................... 5119477 Bytes of storage consumed............. 87928832 Bytes of payload...................... 53617328 61.0% Average payload per entry............. 10.47 Average unused bytes per entry........ 0.04 Average fanout........................ 363.00 Fragmentation......................... 0.81% Maximum payload per entry............. 11 Entries that use overflow............. 0 0.0% Index pages used...................... 59 Primary pages used.................... 21408 Overflow pages used................... 0 Total pages used...................... 21467 Unused bytes on index pages........... 35210 14.6% Unused bytes on primary pages......... 175898 0.20% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 211108 0.24% *** Indices of table XXX ********************************* Percentage of total database.......... 8.6% Number of entries..................... 5119477 Bytes of storage consumed............. 93179904 Bytes of payload...................... 68942864 74.0% Average payload per entry............. 13.47 Average unused bytes per entry........ 1.68 Fragmentation......................... 99.14% Maximum payload per entry............. 14 Entries that use overflow............. 0 0.0% Primary pages used.................... 22749 Overflow pages used................... 0 Total pages used...................... 22749 Unused bytes on primary pages......... 8605625 9.2% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 8605625 9.2% And here is the same output under Linux: *** Table XXX w/o any indices **************************** Percentage of total database.......... 8.1% Number of entries..................... 5119477 Bytes of storage consumed............. 87928832 Bytes of payload...................... 53617328 61.0% Average payload per entry............. 10.47 Average unused bytes per entry........ 0.04 Average fanout........................ 363.00 Fragmentation......................... 0.81% Maximum payload per entry............. 11 Entries that use overflow............. 0 0.0% Index pages used...................... 59 Primary pages used.................... 21408 Overflow pages used................... 0 Total pages used...................... 21467 Unused bytes on index pages........... 35210 14.6% Unused bytes on primary pages......... 175898 0.20% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 211108 0.24% *** Indices of table XXX ********************************* Percentage of total database.......... 8.6% Number of entries..................... 5119477 Bytes of storage consumed............. 93179904 Bytes of payload...................... 68942864 74.0% Average payload per entry............. 13.47 Average unused bytes per entry........ 1.68 Fragmentation......................... 99.08% Maximum payload per entry............. 14 Entries that use overflow............. 0 0.0% Primary pages used.................... 22749 Overflow pages used................... 0 Total pages used...................... 22749 Unused bytes on primary pages......... 8605625 9.2% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 8605625 9.2% _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
