Re: Abnormal performance difference between Postgres and MySQL

On Tue, Feb 24, 2009 at 1:28 AM, Claus Guttesen <kometen(at)gmail(dot)com> wrote:

> > I am doing a performance comparison between running Jena with MySQL and
> > Postgres. I used the 8.3-community version of Postgres and MySQL 5.0.67.
> I
> > have run several queries to both MySQL and Postgres and all of them took
> > similar amount of time to execute except one. For the following query to
> a
> > table having 10,003,728 rows, MySQL takes 0.11 seconds to return results
> > whereas Postgres takes like 1 hour and 20 minutes!
> >
> > Query:
> >
> > 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<http://www.utdallas.edu/%7Efarhan.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;
> >
> > Table:
> >
> > Table "public.jena_g1t1_stmt"
> > Column | Type | Modifiers
> > ---------+--------------------
> > ----+-----------
> > subj | character varying(250) | not null
> > prop | character varying(250) | not null
> > obj | character varying(250) | not null
> > graphid | integer |
> > Indexes:
> > "jena_g1t1_stmt_ixo" btree (obj)
> > "jena_g1t1_stmt_ixsp" btree (subj, prop)
>
> Isn't it missing an index on the column prop?
>
> select ... where A0.Prop='foo' and ...
>
> --
> regards
> Claus
>
> When lenity and cruelty play for a kingdom,
> the gentler gamester is the soonest winner.
>
> Shakespeare
>

Can you please elaborate a bit?

Thanks,

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