Re: mysterious difference in speed when combining two queries with OR

> I should say that this is on postgresql 7.4.16 (debian stable).

	Whoa.

> I cannot understand why the following two queries differ so much in  
> execution time (almost ten times)

	Post EXPLAIN ANALYZE for both, and also post table definitions (with  
indexes), use \d table. This will allow people to help you.

> $ time psql -o /dev/null -f query-a.sql fektest
>
> real    0m2.016s
> user    0m1.532s
> sys     0m0.140s

	You are measuring the time it takes the server to perform the query, plus  
this :
	- time for the client (psql) to launch itself,
	- to read the configuration file,
	- to connect to the server, send the query
	- to transfer the results back to the client (is this on network or local  
? what is the amount of data transferred ?)
	- to process the results, format them as text, display them,
	- to close the connection,
	- to exit cleanly

	As you can see from the above numbers,
	- 2.016 seconds elapsed on your wall clock, of which :
	- 76% was used as CPU time in the client (therefore of absolutely no  
relevance to postgresql server performance)
	- and the rest (24%) distributed in unknown proportion between server CPU  
spent to process your query, network roundtrips, data transfer, server  
iowait, etcetera.

	In order to properly benchmark your query, you should :

	1- Ensure the server is not loaded and processing any other query (unless  
you explicitly intend to test behaviour under load)
	If you don't do that, your timings will be random, depending on how much  
load you have, if someone holds a lock you have to wait on, etc.

	2- ssh to your server and use a psql session local to the server, to  
avoid network roundtrips.

	3- enable statement timing with \t

	2- EXPLAIN your query.

	Check the plan.
	Check the time it took to EXPLAIN, this will tell you how much time it  
takes to parse and plan your query.

	2- EXPLAIN ANALYZE your query.

	Do it several times, note the different timings and understand the query  
plans.
	If the data was not cached, the first timing will be much longer than the  
subsequent other timings. This will give you useful information about the  
behaviour of this query : if lasts for 1 second (cached) and 5 minutes  
(not cached), you might not want to execute it at the same time as that  
huge scheduled backup job. Those timings will also provide hints on wether  
you should CLUSTER the table, etc.

	3- EXPLAIN SELECT count(*) FROM (your query) AS foo
	Check that the plan is the same.

	4- SELECT count(*) FROM (your query) AS foo
	The count(*) means very little data is exchanged between client and  
server, so this doesn't mess with the timing.

	Now, compare :

	The timings displayed by psql (\t) include query planning, roundtrip to  
server, and result processing (hence the count(*) to reduce this overhead).
	The timings displayed by EXPLAIN ANALYZE include only query execution  
time, but EXPLAIN ANALYZE is slower than just executing the query, because  
it takes time to instrument the query and measure its performance. For  
instance, on a very simple query that computes an aggregate on lots of  
rows, more time will be spent measuring than actually executing the query.  
Hence steps 3 and 4 above.

	Knowing this, you deduce the time it takes to parse & plan your query  
(should you then use PREPAREd statements ? up to you) and the time it  
takes to execute it.

	5- EXPLAIN ANALYZE, while changing the parameters (trying some very  
selective or less selective ones) to check for plan change, mess with  
enable_**** parameters to check for different plans, rewrite the query  
differently (DISTINCT/GROUP BY, OR/UNION, JOIN or IN(subquery), etc).