Re: Abnormal performance difference between Postgres and MySQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Feb 23, 2009 at 4:16 PM, Farhan Husain <russoue@xxxxxxxxx> 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'
> 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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux