Date: Tue, 04 Oct 2005 10:45:06 +0200
To: Joe <svn@freedomcircle.net>
Subject: Re: Comparative performance
Cc: pgsql-performance@postgresql.org
References: <6BCB9D8A16AC4241919521715F4D8BCE92E6D6@algol.sollentuna.se>
	<433BDB0B.8010002@freedomcircle.net>
	<Pine.LNX.4.58.0509292230310.26600@linuxworld.com.au>
	<433BE1A0.5000807@freedomcircle.net> <op.sxvguropth1vuj@localhost>
	<4341E328.2060305@freedomcircle.net>
From: PFC <lists@boutiquenumerique.com>
Organization: =?utf-8?Q?La_Boutique_Num=C3=A9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <op.sx349gxcth1vuj@localhost>
In-Reply-To: <4341E328.2060305@freedomcircle.net>
User-Agent: Opera M2/8.0 (Linux, build 1095)


It's more understandable if the table names are in front of the column  
names :

SELECT relationship.topic_id1, relationship.topic_id2, topic.topic_name,  
topic.categ_id, topic.list_name, topic.title,
topic.url, topic.page_type, relationship.rel_type, entry_type.inverse_id,  
relationship.description AS rel_descrip,
relationship.created, relationship.updated
 FROM relationship, topic, entry_type
WHERE
	((relationship.topic_id1 = topic.topic_id AND relationship.topic_id2 =  
1252)
	OR (relationship.topic_id2 =  topic.topic_id and relationship.topic_id1 =  
1252))
	AND	relationship.rel_type = entry_type.type_id
	AND	entry_type.class_id = 2
	ORDER BY rel_type, list_name;

I see a few problems in your schema.
- topic_id1 and topic_id2 play the same role, there is no constraint to  
determine which is which, hence it is possible to define the same relation  
twice.
- as you search on two columns with OR, you need UNION to use indexes.
- lack of indexes
- I don't understand why the planner doesn't pick up hash joins...
- if you use a version before 8, type mismatch will prevent use of the  
indexes.

I'd suggest rewriting the query like this :
SELECT topic.*, foo.* FROM
topic,
(SELECT topic_id2 as fetch_id, topic_id1, topic_id2, rel_type, description  
as rel_descrip, created, updated
	FROM relationship
	WHERE
		rel_type IN (SELECT type_id FROM entry_type WHERE class_id = 2)
	AND	topic_id1 = 1252
UNION
SELECT topic_id1 as fetch_id, topic_id1, topic_id2, rel_type, description  
as rel_descrip, created, updated
	FROM relationship
	WHERE
		rel_type IN (SELECT type_id FROM entry_type WHERE class_id = 2)
	AND	topic_id2 = 1252)
AS foo
WHERE topic.topic_id = foo.fetch_id


CREATE INDEX'es ON
entry_type( class_id )

relationship( topic_id1, rel_type, topic_id2 )	which becomes your new  
PRIMARY KEY
relationship( topic_id2, rel_type, topic_id1 )

> Of course, this doesn't explain how MySQL manages to execute the query  
> in about 9 msec.  The only minor differences in the schema are:   
> entry_type.title and rel_title are char(32) in MySQL,  
> entry_type.class_id is a tinyint, and topic.categ_id, page_type and  
> dark_ind are also tinyints. MySQL also doesn't have the REFERENCES.

Can you post the result from MySQL EXPLAIN ?

You might be interested in the following code. Just replace mysql_ by pg_,  
it's quite useful.

$global_queries_log = array();

function _getmicrotime()	{	list($u,$s) = explode(' ',microtime());	return  
$u+$s;	}

/*	Formats query, with given arguments, escaping all strings as needed.
	db_quote_query( 'UPDATE junk SET a=%s WHERE b=%s', array( 1,"po'po" ) )
	=>	UPDATE junk SET a='1 WHERE b='po\'po'
*/
function db_quote_query( $sql, $params=false )
{
	// if no params, send query raw
	if( !$params )
		return $sql;

	// quote params
	foreach( $params as $key => $val )
	{
		if( is_array( $val ))
			$val = implode( ',', $val );
		$params[$key] = "'".mysql_real_escape_string( $val )."'";
	}
	return vsprintf( $sql, $params );
}

/*	Formats query, with given arguments, escaping all strings as needed.
	Runs query, logging its execution time.
	Returns the query, or dies with error.
*/
function db_query( $sql, $params=false )
{
	// it's already a query
	if( is_resource( $sql ))
		return $sql;

	$sql = db_quote_query( $sql, $params );

	$t = _getmicrotime();
	$r = mysql_query( $sql );
	if( !$r )
	{
		echo "<div class=bigerror><b>Erreur MySQL  
:</b><br>".mysql_error()."<br><br><b>Requ􏻪te</b>  
:<br>".$sql."<br><br><b>Traceback </b>:<pre>";
		foreach( debug_backtrace() as $t ) xdump( $t );
		echo "</pre></div>";
		die();
	}
	global $global_queries_log;
	$global_queries_log[] = array( _getmicrotime()-$t, $sql );
	return $r;
}

At the end of your page, display the contents of $global_queries_log.