On Mon, Feb 23, 2009 at 5:27 PM, Scott Marlowe <scott.marl...@gmail.com>wrote:
> On Mon, Feb 23, 2009 at 4:16 PM, Farhan Husain <russ...@gmail.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
