Re: Abnormal performance difference between Postgres and MySQL

Here is the output:

ingentadb=# EXPLAIN ANALYZE 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://www.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;
                                                                                                QUERY PLAN                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=652089.37..665004.47 rows=733195 width=134) (actual time=5410683.129..5410690.033 rows=30 loops=1)
   Merge Cond: ((a0.subj)::text = (a1.subj)::text)
   ->  Sort  (cost=86716.91..86796.78 rows=31949 width=208) (actual time=76.395..76.423 rows=30 loops=1)
         Sort Key: a0.subj
         Sort Method:  quicksort  Memory: 24kB
         ->  Nested Loop  (cost=0.00..84326.57 rows=31949 width=208) (actual time=4.146..65.409 rows=30 loops=1)
               ->  Index Scan using jena_g1t1_stmt_ixo on jena_g1t1_stmt a0  (cost=0.00..5428.34 rows=487 width=74) (actual time=1.980..2.142 rows=30 loops=1)
                     Index Cond: ((obj)::text = 'Uv::http://www.utdallas.edu/~farhan.husain/IngentaConnect/issue1_1'::text)
                     Filter: (((prop)::text = 'Uv::http://prismstandard.org/namespaces/1.2/basic/isPartOf'::text) AND (graphid = 1))
               ->  Index Scan using jena_g1t1_stmt_ixsp on jena_g1t1_stmt a2  (cost=0.00..161.37 rows=51 width=134) (actual time=2.101..2.104 rows=1 loops=30)
                     Index Cond: (((a2.subj)::text = (a0.subj)::text) AND ((a2.prop)::text = 'Uv::http://prismstandard.org/namespaces/1.2/basic/startingPage'::text))
                     Filter: (a2.graphid = 1)
   ->  Sort  (cost=565372.46..568084.16 rows=1084680 width=74) (actual time=5410606.604..5410606.628 rows=31 loops=1)
         Sort Key: a1.subj
         Sort Method:  quicksort  Memory: 489474kB
         ->  Seq Scan on jena_g1t1_stmt a1  (cost=0.00..456639.59 rows=1084680 width=74) (actual time=0.043..44005.780 rows=3192000 loops=1)
               Filter: ((graphid = 1) AND ((prop)::text = 'Uv::http://www.w3.org/1999/02/22-rdf-syntax-ns#type'::text) AND ((obj)::text = 'Uv::http://metastore.ingenta.com/ns/structure/Article'::text))
 Total runtime: 5410691.012 ms
(18 rows)


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