Re: Abnormal performance difference between Postgres and MySQL

On Wed, Feb 25, 2009 at 4:10 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> >>> Farhan Husain <russoue(at)gmail(dot)com> wrote:
> > The machine postgres is running on has 4 GB of RAM.
>
> In addition to the other suggestions, you should be sure that
> effective_cache_size is set to a reasonable value, which would
> probably be somewhere in the neighborhood of '3GB'. This doesn't
> affect actual RAM allocation, but gives the optimizer a rough idea how
> much data is going to be kept in cache, between both the PostgreSQL
> shared_memory setting and the OS cache. It can make better choices
> with more accurate information.
>
> -Kevin
>

I reran the query with new values of work_mem, effective_cache_size and
shared_buffers. There is no change in runtime. Here is the output:

ingentadb=# show work_mem;
work_mem
----------
16MB
(1 row)

ingentadb=# show shared_buffers;
shared_buffers
----------------
64MB
(1 row)

ingentadb=# show effective_cache_size;
effective_cache_size
----------------------
2GB
(1 row)

ingentadb=# EXPLAIN ANALYZE 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;

QUERY
PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=698313.99..711229.09 rows=733195 width=134) (actual
time=7659407.195..7659418.630 rows=30 loops=1)
Merge Cond: ((a0.subj)::text = (a1.subj)::text)
-> Sort (cost=84743.03..84822.90 rows=31949 width=208) (actual
time=77.269..77.300 rows=30 loops=1)
Sort Key: a0.subj
Sort Method: quicksort Memory: 24kB
-> Nested Loop (cost=0.00..82352.69 rows=31949 width=208) (actual
time=4.821..66.390 rows=30 loops=1)
-> Index Scan using jena_g1t1_stmt_ixo on jena_g1t1_stmt a0
(cost=0.00..5428.34 rows=487 width=74) (actual time=2.334..2.675 rows=30
loops=1)
Index Cond: ((obj)::text = 'Uv::
http://www.utdallas.edu/~farhan.husain/IngentaConnect/issue1_1'::text)
Filter: (((prop)::text = 'Uv::
http://prismstandard.org/namespaces/1.2/basic/isPartOf'::text) AND (graphid
= 1))
-> Index Scan using jena_g1t1_stmt_ixsp on jena_g1t1_stmt
a2 (cost=0.00..157.32 rows=51 width=134) (actual time=2.114..2.119 rows=1
loops=30)
Index Cond: (((a2.subj)::text = (a0.subj)::text) AND
((a2.prop)::text = 'Uv::
http://prismstandard.org/namespaces/1.2/basic/startingPage'::text))
Filter: (a2.graphid = 1)
-> Materialize (cost=613570.96..627129.46 rows=1084680 width=74)
(actual time=7659329.799..7659334.251 rows=31 loops=1)
-> Sort (cost=613570.96..616282.66 rows=1084680 width=74) (actual
time=7659329.781..7659334.185 rows=31 loops=1)
Sort Key: a1.subj
Sort Method: external merge Disk: 282480kB
-> Seq Scan on jena_g1t1_stmt a1 (cost=0.00..456639.59
rows=1084680 width=74) (actual time=0.042..46465.020 rows=3192000 loops=1)
Filter: ((graphid = 1) AND ((prop)::text = 'Uv::
http://www.w3.org/1999/02/22-rdf-syntax-ns#type'::text) AND ((obj)::text =
'Uv::http://metastore.ingenta.com/ns/structure/Article'::text))
Total runtime: 7659420.128 ms
(19 rows)

I will try out other suggestions posted yesterday now.

Thanks,

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