From: PFC <lists@boutiquenumerique.com>
To: Joe <svn@freedomcircle.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Comparative performance
Date: Tue, 04 Oct 2005 10:45:06 +0200
Message-ID: <op.sx349gxcth1vuj@localhost> (raw)
In-Reply-To: <4341E328.2060305@freedomcircle.net>
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>
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>Requte</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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@boutiquenumerique.com, svn@freedomcircle.net
Subject: Re: Comparative performance
In-Reply-To: <op.sx349gxcth1vuj@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox