Re: Abnormal performance difference between Postgres and MySQL

[Farhan Husain <russoue@xxxxxxxxx>]

On Mon, Feb 23, 2009 at 5:27 PM, Gregory Stark <stark@xxxxxxxxxxxxxxxx> wrote:

Farhan Husain <russoue@xxxxxxxxx> writes:

> I can provide any other information needed and also the data if anyone
> wants.

What did the query plans look like in both databases?

In Postgres you can get the query plan with

EXPLAIN ANALYZE select ...

You can leave out the ANALYZE if you can't wait until the query completes but
it will have much less information to diagnosis any problems.

--
 Gregory Stark
 EnterpriseDB          http://www.enterprisedb.com
 Ask me about EnterpriseDB's Slony Replication support!

Here is what I got from MySQL:

mysql> explain Select A0.Subj, A2.Obj From jena_g1t1_stmt A0, jena_g1t1_stmt A1, jena_g1t1_stmt A2 Where A0.Prop='Uv::http://prismstandard.org/namespaces/1.2/basic/isPartOf:' AND A0.Obj='Uv::http://ww
w.utdallas.edu/~farhan.husain/IngentaConnect/issue1_1:' AND A0.GraphID=1 AND A0.Subj=A1.Subj AND A1.Prop='Uv::http://www.w3.org/1999/02/22-rdf-syntax-ns#type:' AND A1.Obj='Uv::http://metastore.ingenta
.com/ns/structure/Article:' AND A1.GraphID=1 AND A0.Subj=A2.Subj AND A2.Prop='Uv::http://prismstandard.org/namespaces/1.2/basic/startingPage:' AND A2.GraphID=1;
+----+-------------+-------+------+------------------------------------+-------------------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys                      | key               | key_len | ref                     | rows | Extra       |
+----+-------------+-------+------+------------------------------------+-------------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | A0    | ref  | jena_g1t1_stmtXSP,jena_g1t1_stmtXO | jena_g1t1_stmtXO  | 102     | const                   |  628 | Using where |
|  1 | SIMPLE      | A1    | ref  | jena_g1t1_stmtXSP,jena_g1t1_stmtXO | jena_g1t1_stmtXSP | 204     | ingentadb.A0.Subj,const |    1 | Using where |
|  1 | SIMPLE      | A2    | ref  | jena_g1t1_stmtXSP                  | jena_g1t1_stmtXSP | 204     | ingentadb.A0.Subj,const |    1 | Using where |
+----+-------------+-------+------+------------------------------------+-------------------+---------+-------------------------+------+-------------+
3 rows in set (0.00 sec)


-- 
Mohammad Farhan Husain
Research Assistant
Department of Computer Science
Erik Jonsson School of Engineering and Computer Science
University of Texas at Dallas