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

Re: Abnormal performance difference between Postgres and MySQL

From: Claus Guttesen <kometen(at)gmail(dot)com>
To: Farhan Husain <russoue(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Abnormal performance difference between Postgres and MySQL
Date: 2009-02-24 07:28:38
Message-ID: b41c75520902232328s2321af46sa4193c89603232c9@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
> 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)

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

In response to

Responses

pgsql-performance by date

Next:From: Grzegorz JaśkiewiczDate: 2009-02-24 14:33:55
Subject: planner's midjudge number of rows resulting, despite pretty obvious join
Previous:From: Farhan HusainDate: 2009-02-24 04:55:46
Subject: Re: Abnormal performance difference between Postgres and MySQL

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