Re: Abnormal performance difference between Postgres and MySQL - Mailing list pgsql-performance

> Here is the latest output:
>
> 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=799852.37..812767.47 rows=733195 width=134) (actual
> time=5941553.710..5941569.192 rows=30 loops=1)
>    Merge Cond: ((a0.subj)::text = (a1.subj)::text)
>    ->  Sort  (cost=89884.41..89964.28 rows=31949 width=208) (actual
> time=243.711..243.731 rows=30 loops=1)
>          Sort Key: a0.subj
>          Sort Method:  quicksort  Memory: 24kB
>          ->  Nested Loop  (cost=0.00..84326.57 rows=31949 width=208) (actual
> time=171.255..232.765 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=96.735..97.070 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..161.37 rows=51 width=134) (actual time=4.513..4.518 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=709967.96..723526.46 rows=1084680 width=74)
> (actual time=5941309.876..5941318.552 rows=31 loops=1)
>          ->  Sort  (cost=709967.96..712679.66 rows=1084680 width=74) (actual
> time=5941309.858..5941318.488 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.054..44604.597 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: 5941585.248 ms
> (19 rows)

Can you do this:

select * from pg_statistic where starelid = 'jena_g1t1_stmt'::regclass;

Thanks,

...Robert