Subject: null and simple retrieve times

From: [email-masked] (STEPHEN CHING-SING YEN)
Date: Wed, 8 Jul 92 09:05:12 -0700

Does anyone out there who knows how to use libpq have any data about
how long postgres takes to do a null query and a single tuple fetch?

example null query - "retrieve (x = 1)" 
example single tuple fetch - "retrieve (emp.all) where emp.id = 123"

Steve Yen
[email-masked]        <--- a new postgres user, not developer!
415-642-9585

---

From: postarch (Postgres Mailing Archive)
Date: Tue, 21 Jul 92 11:52:11 PDT

> example single tuple fetch - "retrieve (emp.all) where emp.id = 123"

The execution times for both of these queries will be very dependent on
whether POSTGRES is warm or cold (e.g. is this the first query or
have several other queries been run?).  POSTGRES does many cache lookups
on intialization, and every miss means we have to go to disk.

Jeff Meredith
[email-masked]

---

From: postarch (Postgres Mailing Archive)
Date: Tue, 21 Jul 92 22:55:08 PDT

> example single tuple fetch - "retrieve (emp.all) where emp.id = 123"

I realized tonight while doing my dinner dishes that my earlier reply
to this wasn't very good or very helpful.  It is true that timings of 
single queries will depend heavily on whether or not postgres is warmed
up, but that doesn't answer your question.  The real answer is that you
should just run the experiment for yourself.  Using libpq isn't hard,
here are 2 simple programs:

main()
{
	PQsetdb("yourdb");
	PQexec("retrieve (emp.all) where emp.id = 123");
	PQfinish();
	exit(0);
}

main()
{
	PQsetdb("yourdb");
	PQexec("retrieve (x=1)");
	PQfinish();
	exit(0);
}

I used the following commands to build and run the second program:

	$ cc -o null -L/private/mer/pg/O  prog.c -lpq 

	$ time ./null
		1.2 real         0.0 user         0.0 sys  

So it takes roughly 1.2 seconds to get a fresh backend started and run the
null query (on a DECstation 5000 running Ultrix 4.2A, postgres compiled with
Assert checking and w/o optimization).

Jeff

p.s. the first program's execution time will also depend on the size of
     the emp relation, whether or not you have indices defined on the id
     attribute, and probably 1000 other things I'm not considering.