Optimizing query: select ... where id = 4 and md5(...) = '...'

Hello,

in my application I'm trying to authenticate users
against a table called "users". The integer column
"id" should match, but also an md5 hash of the
"password" column (salted with a string) should match.
My authentication function (written in C, using libpq)
should return a "username" (is a varchar(200) field).

I wonder, what is faster: fetching 2 columns - the
username and the md5-result and then comparing the
md5 string against the argument in my app, like here:

punbb=> select username, md5('deadbeef' || password) from users where id = 4;
 username |               md5
----------+----------------------------------
 Vasja    | dcde745cc304742e26d62e683a9ecb0a
(1 row)

punbb=> explain select username, md5('deadbeef' || password) from
users where id = 4;
                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using users_pkey on users  (cost=0.00..5.95 rows=1 width=156)
   Index Cond: (id = 4)
(2 rows)

Or letting the database doing this comparison for me:

punbb=> select username from users where id = 4 and md5('deadbeef' ||
password) = 'dcde745cc304742e26d62e683a9ecb0a';
 username
----------
 Vasja
(1 row)

punbb=> explain select username from users where id = 4 and
md5('deadbeef' || password) = 'dcde745cc304742e26d62e683a9ecb0a';
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Index Scan using users_pkey on users  (cost=0.00..5.95 rows=1 width=118)
   Index Cond: (id = 4)
   Filter: (md5(('deadbeef'::text || ("password")::text)) =
'dcde745cc304742e26d62e683a9ecb0a'::text)
(3 rows)

I've prepared a test case with the code listed at the
botom and have run it 1000 times, but am still unsure:

$ time perl -e 'for (1..1000) {system("./fetch-user", "APP_QUERY") and die $!}'
....
username: Vasja
....
5.038u 5.734s 0:26.29 40.9%     0+0k 0+4io 0pf+0w

$ time perl -e 'for (1..1000) {system("./fetch-user", "DB_QUERY") and die $!}'
....
username: Vasja
....
4.757u 5.890s 0:26.52 40.1%     0+0k 0+8io 0pf+0w

How does one profile PostgreSQL-queries in general?

Thank you
Alex

PS: Using Postgresql 8.1.0 (from packages) on OpenBSD/386 -current

PPS: My test program, call with APP_QUERY or DB_QUERY:

#include <err.h>
#include <stdio.h>
#include <libpq-fe.h>

#define DB_CONN_STR     "host=/var/www/tmp user=punbb dbname=punbb"
#define APP_QUERY       "select username, md5('deadbeef' || password) " \
                        "from users where id = $1"
#define DB_QUERY        "select username from users where id = $1 and " \
                        "md5('deadbeef' || password) = $2"
int
main(int argc, char *argv[])
{
        PGconn          *conn;
        PGresult        *res;
        const char      *query;
        const char      *args[2];
        unsigned        nargs;
        char            username[201];

        if (! strcmp(argv[1], "APP_QUERY")) {
                query = APP_QUERY;
                nargs = 1;
        } else if (! strcmp(argv[1], "DB_QUERY")) {
                query = DB_QUERY;
                nargs = 2;
        } else
                errx(1, "wrong usage: supply APP_QUERY or DB_QUERY");

        if ((conn = PQconnectdb(DB_CONN_STR)) == NULL)
                err(1, "Connect failed: out of memory");

        if (PQstatus(conn) != CONNECTION_OK)
                err(1, "Connect failed: %s", PQerrorMessage(conn));

        if ((res = PQprepare(conn, "sql_fetch_username",
            query, nargs, NULL)) == NULL)
                err(1, "Preparing '%s' failed: out of memory", query);

        if (PQresultStatus(res) != PGRES_COMMAND_OK)
                err(1, "Preparing statement failed: %s", PQerrorMessage(conn));

        PQclear(res);

        args[0] = "4";
        args[1] = "dcde745cc304742e26d62e683a9ecb0a";

        if ((res = PQexecPrepared(conn, "sql_fetch_username",
            nargs, args, NULL, NULL, 0)) == NULL)
                err(1, "Executing statement '%s' failed: out of memory",
                    query);

        if (PQresultStatus(res) != PGRES_TUPLES_OK)
                err(1, "Executing statement '%s' failed: %s",
                    query, PQerrorMessage(conn));
                PQclear(res);

        if (nargs == 1)
                (void) strcmp(args[1], PQgetvalue(res, 0, 1));

        fprintf(stderr, "username: %s\n", PQgetvalue(res, 0, 0));

        PQfinish(conn);
        return 0;
}


--
http://preferans.de