Skip site navigation (1) Skip section navigation (2)

Re: Abnormal performance difference between Postgres and MySQL

From: Farhan Husain <russoue(at)gmail(dot)com>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Abnormal performance difference between Postgres and MySQL
Date: 2009-02-23 23:33:06
Message-ID: 3df32b6d0902231533s7d0277f4rb2e2ef8896ad8f29@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Feb 23, 2009 at 5:27 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Mon, Feb 23, 2009 at 4:16 PM, Farhan Husain <russoue(at)gmail(dot)com> wrote:
> > Hello,
> >
> > 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)
> >
> > Machine: SunOS 5.10 Generic_127111-11 sun4u sparc SUNW, Sun-Fire-880
> > Memory: 4 GB
> > Number of physical processors: 2
> >
> > I tried to re-arrage the query but each time the amount of time needed is
> > the same. Can anyone help me find the answer to why Postgres is taking so
> > much time?
> >
> > I can provide any other information needed and also the data if anyone
> > wants.
>
> What is the locale of your database?  I.e.:
>
> # show lc_collate ;
>  lc_collate
> -------------
>  en_US.UTF-8
> (1 row)
>
> If it's not C then string compares are going to probably need special
> indexes to work the way you expect them. (varchar pattern ops).  Look
> here for more information:
>
> http://www.postgresql.org/docs/8.3/static/indexes-opclass.html
>

Here it is:

ingentadb=# show lc_collate;
   lc_collate
-----------------
 en_US.ISO8859-1
(1 row)

Do you think this is the source of the problem?

Thanks,

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

In response to

pgsql-performance by date

Next:From: Guillaume SmetDate: 2009-02-23 23:33:33
Subject: Re: Abnormal performance difference between Postgres and MySQL
Previous:From: Scott MarloweDate: 2009-02-23 23:27:52
Subject: Re: Abnormal performance difference between Postgres and MySQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group