Re: speeding up a query - Mailing list pgsql-general

Hi again,

I was thinking, in my slow query it seems the sorting is the villain.
Doing a simple qsort test I notice that:
ehsmeng@menglap /cygdrive/c/pond/dev/tt
$ time ./a.exe 430

real    0m0.051s
user    0m0.030s
sys     0m0.000s

ehsmeng@menglap /cygdrive/c/pond/dev/tt
$ time ./a.exe 430000

real    0m0.238s
user    0m0.218s
sys     0m0.015s

ehsmeng@menglap /cygdrive/c/pond/dev/tt
$ time ./a.exe 4300000

real    0m2.594s
user    0m2.061s
sys     0m0.108s

 From this very unfair test indeed I see that my machine has the
capability to sort 4.3 million entries during the same time my pg is
sorting 430.

And i cannot stop wondering if there is some generic sorting routine
that is incredibly slow? Would it be possible to, in the situations
where order by is by simple datatypes of one column, to do a special
sorting, like the qsort example in the end of this mail? Is this already
addressed in later versions?

If no, why? and if yes, where in the pg code do I look?

Best regards,
Marcus


#include <stdio.h>
#include <stdlib.h>

typedef struct {
    int     val;
    void   *pek;
} QSORTSTRUCT_INT_S;

int sortstruct_int_compare(void const *a, void  const *b)
{
    return ( ((QSORTSTRUCT_INT_S *)a)->val - ((QSORTSTRUCT_INT_S
*)b)->val );
}

int main (int argc, char **argv)
{
    int nbr = 0;
    int i = 0;
    QSORTSTRUCT_INT_S *sort_arr = 0;
    if (1 == argc) {
        printf("forgot amount argument\n");
        exit(1);
    }
    nbr = atoi (argv[1]);
    if (0 == (sort_arr = malloc (sizeof(QSORTSTRUCT_INT_S) * nbr))) {
        printf("cannot alloc\n");
        exit(1);
    }
    srand(123);
    for (i=0; i<nbr; i++) {
        sort_arr[i].val = rand();
    }
    qsort(sort_arr, nbr, sizeof(QSORTSTRUCT_INT_S),sortstruct_int_compare);
    return 0;
}