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

["Alexander Farber" <alexander.farber@xxxxxxxxx>]

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