Re: Comparative performance - Mailing list pgsql-performance

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.