Re: Index Scans become Seq Scans after VACUUM ANALYSE

Apologies for the naff double post, but I meant to add that obviously
the figures for the solaris box are bogus after the first run...imagine
a file system cache of an entire 2gb file. I tried creating a file of
4gb on this box, but it bombed with a "file too large error".
Unfortunately, I can't rip memory out of this box as I don't have
exclusive access.

On Thu, 2002-04-18 at 11:49, Mark Pritchard wrote:
> I threw together the attached program (compiles fine with gcc 2.95.2 on
> Solaris 2.6 and egcs-2.91.66 on RedHat Linux 6.2) and ran it a few
> times. Data is below. Usual disclaimers about hastily written code etc
> :)
> 
> Machine = ghoul (generic intel, 384mb ram, dual p3-800, ide disk running
> dma)
> 
> Sequential
> Bytes Read	Time	Bytes / Sec
> 536870912       27.14   19783933.74
> 536870912       27.14   19783990.60
> 536870912       27.11   19801872.14
> 536870912       26.92   19942928.41
> 536870912       27.31   19657408.43
>                         19794026.66 (avg)
> 
> Random		
> Bytes Read	Time	Bytes / Sec
> 1073741824      519.57  2066589.21
> 1073741824      517.78  2073751.44
> 1073741824      516.92  2077193.23
> 1073741824      513.18  2092333.29
> 1073741824      510.68  2102579.88
>                         2082489.41 (avg)
> 
> Machine = jedi (Sun E420, 3gb ram, dual 400s, test on single scsi disk)
> 
> Sequential		
> Bytes Read	Time	Bytes / Sec
> 2097152000      65.19   32167675.28
> 2097152000      65.22   32154114.65
> 2097152000      65.16   32182561.99
> 2097152000      65.12   32206105.12
> 2097152000      64.67   32429463.26
>                         32227984.06 (avg)
> 
> Random		
> Bytes Read	Time	Bytes / Sec
> 4194304000      1522.22 2755394.79
> 4194304000      278.18  15077622.05
> 4194304000      91.43   45874730.07
> 4194304000      61.43   68273795.19
> 4194304000      54.55   76890231.51
>                         41774354.72
> 
> If I interpret Tom's "divide" instruction correctly, is that a factor of
> 10 on the linux box?
> 
> On Thu, 2002-04-18 at 01:16, Tom Lane wrote:
> > "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es> writes:
> > > On my own few experience I think this could be solved decreasing
> > > random_page_cost, if you would prefer to use indexes than seq scans, then
> > > you can lower random_page_cost to a point in which postgres works as you
> > > want. So the planner would prefer indexes when in standard conditions it
> > > would prefer seq scans.
> > 
> > It's entirely possible that the default value of random_page_cost is too
> > high, at least for many modern machines.  The experiments I did to get
> > the 4.0 figure were done a couple years ago, on hardware that wasn't
> > exactly new at the time.  I have not heard of anyone else trying to
> > measure it though.
> > 
> > I don't think I have the source code I used anymore, but the principle
> > is simple enough:
> > 
> > 1. Make a large file (several times the size of your machine's RAM, to
> > ensure you swamp out kernel disk buffering effects).  Fill with random
> > data. (NB: do not fill with zeroes, some filesystems optimize this away.)
> > 
> > 2. Time reading the file sequentially, 8K per read request.
> > Repeat enough to get a statistically trustworthy number.
> > 
> > 3. Time reading randomly-chosen 8K pages from the file.  Repeat
> > enough to get a trustworthy number (the total volume of pages read
> > should be several times the size of your RAM).
> > 
> > 4. Divide.
> > 
> > The only tricky thing about this is making sure you are measuring disk
> > access times and not being fooled by re-accessing pages the kernel still
> > has cached from a previous access.  (The PG planner does try to account
> > for caching effects, but that's a separate estimate; the value of
> > random_page_cost isn't supposed to include caching effects.)  AFAIK the
> > only good way to do that is to use a large test, which means it takes
> > awhile to run; and you need enough spare disk space for a big test file.
> > 
> > It'd be interesting to get some numbers for this across a range of
> > hardware, filesystems, etc ...
> > 
> > 			regards, tom lane
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> > 
> 
> 
> ----
> 

> #include <errno.h>
> #include <stdio.h>
> #include <stdlib.h>
> #include <time.h>
> #include <sys/stat.h>
> #include <sys/time.h>
> 
> /**
>  * Constants
>  */
> 
> #define BLOCK_SIZE		(8192)
> 
> /**
>  * Prototypes
>  */
> 
> 	// Creates the test file filled with random data
> 	void createTestFile(char *testFileName, long long fileSize);
> 
> 	// Handles runtime errors by displaying the function, activity and error number
> 	void handleError(char *functionName, char *activity);
> 
> 	// Standard entry point
> 	int main(int argc, char *args[]);
> 
> 	// Prints correct usage and quits
> 	void printUsageAndQuit();
> 
> 	// Tests performance of random reads of the given file
> 	void testRandom(char *testFileName, long long amountToRead);
> 
> 	// Tests performance of sequential reads of the given file
> 	void testSeq(char *testFileName);
> 
> /**
>  * Definitions
>  */
> 
> /**
>  * createTestFile()
>  */
> void createTestFile(char *testFileName, long long fileSize)
> {
> 	FILE *testFile;
> 	long long reps, i, j, bufferReps;
> 	time_t timetmp;
> 	long long *buffer;
> 	size_t written;
> 
> 	// Indicate op
> 	printf("Creating test file %s of %lld mb\n",testFileName,fileSize);
> 
> 	// Adjust file size to bytes
> 	fileSize *= (1024*1024);
> 
> 	// Allocate a buffer for writing out random long longs
> 	if (!(buffer = malloc(BLOCK_SIZE)))
> 		handleError("createTestFile()","malloc");
> 
> 	// Open the file for writing
> 	if (!(testFile = fopen(testFileName, "wb")))
> 		handleError("createTestFile()","fopen");
> 
> 	// Initialise the random number generator
> 	srandom(time(NULL));
> 
> 	// Write data
> 	reps 		= fileSize / BLOCK_SIZE;
> 	bufferReps	= BLOCK_SIZE / sizeof(long long);
> 	for (i = 0; i < reps; i++)
> 	{
> 		// Fill buffer with random data
> 		for (j = 0; j < bufferReps; j++)
> 			buffer[j] = random();
> 
> 		// Write
> 		written = fwrite(buffer, sizeof(long long), bufferReps, testFile);
> 		if (written != bufferReps)
> 			handleError("createTestFile()","fwrite");
> 	}
> 
> 	// Flush and close
> 	if (fflush(testFile))
> 		handleError("createTestFile()","fflush");
> 	if (fclose(testFile))
> 		handleError("createTestFile()","fclose");
> 
> 	// Free buffer
> 	free(buffer);
> }
> 
> /**
>  * handleError()
>  */
> void handleError(char *functionName, char *activity)
> {
> 	fprintf(stderr, "Error in %s while attempting %s. Error %d (%s)\n", functionName, activity, errno, strerror(errno));
> 	exit(1);
> }
> 
> /**
>  * main()
>  */
> int main(int argc, char *argv[])
> {
> 	// Print usage and quit if argument count is definitely incorrect
> 	if (argc < 3)
> 	{
> 		// Definitely wrong
> 		printUsageAndQuit();
> 	}
> 	else
> 	{
> 		// Dispatch
> 		if (!strcmp(argv[1], "create"))
> 		{
> 			if (argc != 4)
> 				printUsageAndQuit();
> 
> 			// Create the test file of the specified size
> 			createTestFile(argv[2], atol(argv[3]));
> 		}
> 		else if (!strcmp(argv[1], "seqtest"))
> 		{
> 			if (argc != 3)
> 				printUsageAndQuit();
> 
> 			// Test performance of sequential reads
> 			testSeq(argv[2]);
> 		}
> 		else if (!strcmp(argv[1], "rndtest"))
> 		{
> 			if (argc != 4)
> 				printUsageAndQuit();
> 
> 			// Test performance of random reads
> 			testRandom(argv[2], atol(argv[3]));
> 		}
> 		else
> 		{
> 			// Unknown command
> 			printUsageAndQuit();
> 		}
> 	}
> 
> 	return 0;
> }
> 
> /**
>  * printUsageAndQuit()
>  */
> void printUsageAndQuit()
> {
> 	puts("USAGE: rndpgcst [create <file> <size_in_mb>] | [seqtest <file>] | [rndtest <file> <read_in_mb>]");
> 
> 	exit(1);
> }
> 
> /**
>  * testSeq()
>  */
> void testSeq(char *testFileName)
> {
> 	FILE *testFile;
> 	char *buffer;
> 	long long reps, totalRead, thisRead, timeTaken;
> 	struct timeval startTime, endTime;
> 	struct timezone timezoneDiscard;
> 
> 	// Indicate op
> 	printf("Sequential read test of %s\n",testFileName);
> 
> 	// Grab a buffer
> 	buffer = malloc(BLOCK_SIZE);
> 
> 	// Open the file for reading
> 	if (!(testFile = fopen(testFileName, "rb")))
> 		handleError("testSeq()","fopen");
> 
> 	// Start timer
> 	if (gettimeofday(&startTime, &timezoneDiscard) == -1)
> 		handleError("testSeq()", "gettimeofday start");
> 
> 	// Read all data from file
> 	totalRead = 0;
> 	while ((thisRead = fread(buffer, 1, BLOCK_SIZE, testFile)) != 0)
> 		totalRead += thisRead;
> 
> 	// End timer
> 	if (gettimeofday(&endTime, &timezoneDiscard) == -1)
> 		handleError("testSeq()", "gettimeofday start");
> 
> 	// Close
> 	if (fclose(testFile))
> 		handleError("testSeq()","fclose");
> 
> 	// Free the buffer
> 	free(buffer);
> 
> 	// Display time taken
> 	timeTaken = (endTime.tv_sec - startTime.tv_sec) * 1000000;
> 	timeTaken += (endTime.tv_usec - startTime.tv_usec);
> 	printf("%lld bytes read in %f seconds\n", totalRead, (double) timeTaken / (double) 1000000);
> }
> 
> /**
>  * testRandom()
>  */
> void testRandom(char *testFileName, long long amountToRead)
> {
> 	FILE *testFile;
> 	long long reps, i, fileSize, timeTaken, totalRead, readPos, thisRead, offsetMax;
> 	struct stat fileStats;
> 	char *buffer;
> 	struct timeval startTime, endTime;
> 	struct timezone timezoneDiscard;
> 
> 	// Indicate op
> 	printf("Random read test of %s for %lld mb\n", testFileName, amountToRead);
> 
> 	// Initialise the random number generator
> 	srandom(time(NULL));
> 
> 	// Adjust amount to read
> 	amountToRead *= (1024*1024);
> 
> 	// Determine file size
> 	if (stat(testFileName, &fileStats) == -1)
> 		handleError("testRandom()", "stat");
> 	fileSize = fileStats.st_size;
> 
> 	// Grab a buffer
> 	buffer = malloc(BLOCK_SIZE);
> 
> 	// Open the file for reading
> 	if (!(testFile = fopen(testFileName, "rb")))
> 		handleError("testRandom()","fopen");
> 
> 	// Start timer
> 	if (gettimeofday(&startTime, &timezoneDiscard) == -1)
> 		handleError("testRandom()", "gettimeofday start");
> 
> 	// Read data from file
> 	reps 		= amountToRead / BLOCK_SIZE;
> 	offsetMax	= fileSize / BLOCK_SIZE;
> 	for (i = 0; i < reps; i++)
> 	{
> 		// Determine read position
> 		readPos = (random() % offsetMax) * BLOCK_SIZE;
> 
> 		// Seek and read
> 		if (fseek(testFile, readPos, SEEK_SET) == -1)
> 			handleError("testRandom()","fseek");
> 		if ((thisRead = fread(buffer, 1, BLOCK_SIZE, testFile)) != BLOCK_SIZE)
> 			handleError("testRandom()","fread");
> 	}
> 
> 	// End timer
> 	if (gettimeofday(&endTime, &timezoneDiscard) == -1)
> 		handleError("testRandom()", "gettimeofday start");
> 
> 	// Close
> 	if (fclose(testFile))
> 		handleError("testRandom()","fclose");
> 
> 	// Free the buffer
> 	free(buffer);
> 
> 	// Display time taken
> 	timeTaken = (endTime.tv_sec - startTime.tv_sec) * 1000000;
> 	timeTaken += (endTime.tv_usec - startTime.tv_usec);
> 	printf("%lld bytes read in %f seconds\n", amountToRead, (double) timeTaken / (double) 1000000);
> }