Re: Abnormal performance difference between Postgres and MySQL

On Mon, Feb 23, 2009 at 5:27 PM, Gregory Stark <stark(at)enterprisedb(dot)com>wrote:

> Farhan Husain <russoue(at)gmail(dot)com> 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