Odd performance results - more info - Mailing list pgsql-performance

I didn't get any helpful responses to my previous email so I thought
I would try again, this time with example code.

Below is my orignal email and code for a dead simple ASCII menu driven
application that demonstrates the problem.  The app starts up with a
menu of 4 items to create the test databases, to run the float or int
test or to quit.  The databases created are named "int_db" and "float_db"
and contain just 1 table and an index on that table,  The tables are
populated with 2000 records and the "test" menu options perform
an UPDATE on each of the 2000 records.  Postmaster is assumed
to be running on "localhost".  You only need to select the "Create
databases" option on the menu once.

I would greatly appreciate it if someone could run this code in their
environment and let me know if you get results similiar to mine.
The INT test results in execution times of 11 - 50+ secs increasing
each time the test is run.  The FLOAT test execution times are
consistently < 3 secs regardless of how many times it is run.

I hope this piques someones curiosity.  I'd really like to know
what is going on here...


***********************************************************************
Medora Schauer
Sr. Software Engineer

Fairfield Industries
Sugar Land, Tx  77478-3469

mschauer@fairfield.com
***********************************************************************

original email:

************************************************************************************
I have a table with a 3 column key.  I noticed that when I update a non-key field
in a record of the table that the update was taking longer than I thought it
should.  After much experimenting I discovered that if I changed the data
types of two of the key columns to FLOAT8 that I got vastly improved
performance.

Orignally the data types of the 3 columns were FLOAT4, FLOAT4 and INT4.
My plaform is a PowerPC running Linux.  I speculated that the performance
improvement might be because the PowePC is a 64 bit processor but when
I changed the column data types to INT8, INT8 and INT4 I din't see any
improvement.  I also ran my test code on a Pentium 4 machine with the same
results in all cases.

This doesn't make any sense to me.  Why would FLOAT8 keys ever result
in improved performance?

I verified with EXPLAIN that the index is used in every case for the update.

My postmaster version is 7.1.3.

Any help will be greatly appreciated.

*************************************************************************************

test code:

#include <stdlib.h>
#include <errno.h>
#include <string.h>
#include <libpq-fe.h>
#include <time.h>
#include <sys/time.h>
#include <unistd.h>

#ifndef TRUE
#define TRUE (1)
#define FALSE (0)
#endif

#define INT_TYPE (0)
#define FLOAT_TYPE (1)

char buffer[200];
PGconn *fconn = NULL, *iconn = NULL;

PGconn *CreateTestDb(int dtype);
void AddRecords(PGconn *conn, int dtype);
PGconn *Connect(char *dbname);
int DoSql(PGconn *conn, char *query, PGresult **result);
long FindExeTime(struct timeval *start_time, struct timeval *end_time);
void UpdateTraceCounts(int dtype);


int
main (int argc, char **argv) {
    char database[40];
    char option[10];
    int quit = FALSE;
    char response[100];

    while (!quit){
        printf ("\n\n\n" \
                "c - Create databases.\n" \
                "f - Run FLOAT8 test.\n" \
                "i - Run INT8 test.\n" \
                "q - Quit\n\n" \
                "Selection : ");
        gets(option);

        switch (option[0]){
            case 'c' :
            printf ("Starting creation of FLOAT8 database...\n");
            if ((fconn = CreateTestDb(FLOAT_TYPE)) == NULL){
                printf ("\n#### ERROR #### : Counldn't build FLOAT8 db.\n");
            } else {
                printf ("Adding 2000 records to FLOAT8 database...\n");
                AddRecords(fconn, FLOAT_TYPE);
                printf ("\nFLOAT8 Db created.\n");
            }

            printf ("Starting creation of INT8 database...\n");
            if ((iconn = CreateTestDb(INT_TYPE)) == NULL){
                printf ("\n#### ERROR #### : Counldn't build INT8 db.\n");
            } else {
                printf ("Adding 2000 records to INT8 database...\n");
                AddRecords(iconn, INT_TYPE);
                printf ("\nINT8 Db created.\n");
            }
            break;

            case 'f' :
            printf ("Updating 2000 records in FLOAT8 database...\n");
            UpdateTraceCounts(FLOAT_TYPE);
            break;

            case 'i' :
            printf ("Updating 2000 records in INT8 database...\n");
            UpdateTraceCounts(INT_TYPE);
            break;

            case 'q' :
            quit = TRUE;
            break;

            default:
            printf ("Invalid option.\n");
        }
    }

    if (iconn) PQfinish(iconn);
    if (fconn) PQfinish(fconn);
    exit (0);
}


void
UpdateTraceCounts(int dtype){
    int i, last, status;
    PGresult *result;
    struct timeval exe_begin, exe_end, exe_begin2, exe_end2;
    long exe_time, exe_time2;
    int shotpoint, shotline;
    PGconn *conn;

    if (dtype == FLOAT_TYPE){
        if (fconn == NULL){
            if ((fconn = Connect("float_db")) == NULL){
                printf ("#### ERROR #### : Cannot connect to float_db database.\n");
                return;
            }
        }
        conn = fconn;
    } else {
        if (iconn == NULL){
            if ((iconn = Connect("int_db")) == NULL){
                printf ("#### ERROR #### : Cannot connect to int_db database.\n");
                return;
            }
        }
        conn = iconn;
    }

    last = 2000;

    gettimeofday(&exe_begin2, NULL);

    if ((status = DoSql(conn, "BEGIN TRANSACTION", &result)) != 0){
        printf("#### ERROR ####  Error starting database transaction.\n");
        if (result) PQclear(result);
    }

    gettimeofday(&exe_begin, NULL);

    shotline = 1;
    shotpoint = 10001;
    for (i = 0; i < last; i++){
        if (dtype == INT_TYPE){
            snprintf(buffer, sizeof(buffer),
             "UPDATE shot_record SET trace_count = %d " \
             "WHERE shot_line_num = %d " \
             "  AND shotpoint = %d " \
             "  AND index = %d" ,
             0, shotline, shotpoint + i, 0);
        } else {
            snprintf(buffer, sizeof(buffer),
             "UPDATE shot_record SET trace_count = %d " \
             "WHERE shot_line_num = %f " \
             "  AND shotpoint = %f " \
             "  AND index = %d" ,
             0, (float)shotline, (float)shotpoint + (float)i, 0);
        }

        status = DoSql(conn, buffer,  &result);
        if (status != 0){
            printf ("#### ERROR #### : Error updating db.\n");
            break;
        }
    }

    gettimeofday(&exe_end, NULL);

    if ((status = DoSql(conn, "COMMIT TRANSACTION", &result)) != 0){
        printf("#### ERROR #### : Error commiting database transaction.\n");
        if (result) PQclear(result);
    }

    gettimeofday(&exe_end2, NULL);

    exe_time = FindExeTime(&exe_begin, &exe_end);
    exe_time2 = FindExeTime(&exe_begin2, &exe_end2);

    printf ("time to complete updates: %ld msec\n", exe_time);
    printf ("total time: %ld msec\n", exe_time2);
}

PGconn *
CreateTestDb(int dtype){
    PGconn *conn;
    int status;
    PGresult *result;
    char *dbname;
    char *icmd_strings[] = {
                          "CREATE TABLE shot_record ( " \
                          "shot_line_num INT8, " \
                          "shotpoint INT8, " \
                          "index INT2, " \
                          "trace_count INT4,  " \
                          "PRIMARY KEY (shot_line_num, shotpoint, index)) ",
                          NULL};
    char *fcmd_strings[] = {
                          "CREATE TABLE shot_record ( " \
                          "shot_line_num FLOAT8, " \
                          "shotpoint FLOAT8, " \
                          "index INT2, " \
                          "trace_count INT4,  " \
                          "PRIMARY KEY (shot_line_num, shotpoint, index))",
                          NULL};

    char **cmdP;

    if (dtype == INT_TYPE){
        cmdP = icmd_strings;
        dbname = "int_db";
    } else {
        cmdP = fcmd_strings;
        dbname = "float_db";
    }

    /* Open a connection to the template1 database. */
    if ((conn = Connect("template1")) == NULL){
        fprintf(stderr, "Database connect query: %s\n", buffer);
        return(NULL);
    }

    /* Create the new database. */
    snprintf(buffer, sizeof(buffer), "CREATE DATABASE %s", dbname);
    result = PQexec(conn, buffer);

    if (!result || PQresultStatus(result) != PGRES_COMMAND_OK){
        if (result){
            PQclear(result);
        }
        fprintf(stderr, "%s\n    %s\n", buffer, PQresultErrorMessage(result));
        PQfinish(conn);
        return(NULL);
    }

    PQclear(result);
    PQfinish(conn);

    /* Open a connection to the new database. */
    if ((conn = Connect(dbname)) == NULL){
        return (NULL);
    }

    /* Create the database tables. */
    while (*cmdP){
        if ((status = DoSql(conn, *cmdP, &result)) != 0){
            fprintf(stderr,"%s\n%s\n",
                    *cmdP, PQresultErrorMessage(result));
            PQfinish(conn);
            return(NULL);
        } else {
            PQclear(result);
        }
        ++cmdP;
    }

    return(conn);
}

void
AddRecords(PGconn *conn, int dtype){
    int num_shots, i;
    PGresult *result;
    int shotpoint;

    /* Add a bunch of records to the table. */
    num_shots = 2000;

    shotpoint = 10001;
    for (i = 0; i < num_shots; i++){

       if (dtype == INT_TYPE){
           sprintf (buffer,  "INSERT INTO Shot_Record VALUES (1, %d, 0, 0)",
                          shotpoint + i, shotpoint + i);
       } else {
           sprintf (buffer,  "INSERT INTO Shot_Record VALUES (1.0, %f, 0, 0) ",
                          shotpoint + (float)i, shotpoint + (float)i);
       }
        result = PQexec(conn, buffer);

        if (!result || PQresultStatus(result) != PGRES_COMMAND_OK){
            if (result){
                PQclear(result);
            }
            fprintf(stderr, "%s\n    %s\n", buffer,PQresultErrorMessage(result));
            PQfinish(conn);
            return;
        }
    }
}

PGconn *
Connect(char *dbname){
    char *func = "Connect()";
    PGconn *conn;

    snprintf (buffer, sizeof(buffer), "host='localhost' dbname='%s'", dbname);
    conn = PQconnectdb(buffer);

    if (PQstatus(conn) != CONNECTION_OK){
        fprintf(stderr, "%s: Database connect query: %s\n", func, buffer);
        return(NULL);
    }

    return (conn);
}

int
DoSql(PGconn *conn, char *query, PGresult **result){
    char *func = "DoSql()";
    int status = 0;

    if (!conn){
        return(-1);
    }

    *result = PQexec(conn, query);
    if (!*result || ((PQresultStatus(*result) != PGRES_COMMAND_OK)
                     && (PQresultStatus(*result) != PGRES_TUPLES_OK))){
        if (*result){
            fprintf(stderr, "%s: %s\n%s\n", func, query, PQresultErrorMessage(*result));
            PQclear(*result);
            *result = NULL;
        }

        /* See if the database connection is valid. */
        if (PQstatus(conn) == CONNECTION_BAD){
            status = -1;
        } else {
            status = -2;
        }
    } else {
        status = 0;
    }

    return(status);
}

#define DB_TIME_CONVERT (1000)

long
FindExeTime(struct timeval *start_time, struct timeval *end_time){
    long exe_time;

    exe_time = ((1000000 * end_time->tv_sec) + end_time->tv_usec) -
            ((1000000 * start_time->tv_sec) + start_time->tv_usec);

    return(exe_time/DB_TIME_CONVERT);
}