Re: Abnormal performance difference between Postgres and MySQL - Mailing list pgsql-performance
| From | Farhan Husain |
|---|---|
| Subject | Re: Abnormal performance difference between Postgres and MySQL |
| Date | |
| Msg-id | 3df32b6d0902231724h5b449197g2368502e04c2ea4c@mail.gmail.com Whole thread Raw |
| In response to | Re: Abnormal performance difference between Postgres and MySQL (Gregory Stark <stark@enterprisedb.com>) |
| Responses |
Re: Abnormal performance difference between Postgres and
MySQL
(Scott Marlowe <scott.marlowe@gmail.com>)
Re: Abnormal performance difference between Postgres and MySQL (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-performance |
On Mon, Feb 23, 2009 at 5:27 PM, Gregory Stark <stark@enterprisedb.com> wrote:
Farhan Husain <russoue@gmail.com> writes:What did the query plans look like in both databases?
> I can provide any other information needed and also the data if anyone
> wants.
In Postgres you can get the query plan with
EXPLAIN ANALYZE select ...
You can leave out the ANALYZE if you can't wait until the query completes but
it will have much less information to diagnosis any problems.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
Here is the 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=652089.37..665004.47 rows=733195 width=134) (actual time=5410683.129..5410690.033 rows=30 loops=1)
Merge Cond: ((a0.subj)::text = (a1.subj)::text)
-> Sort (cost=86716.91..86796.78 rows=31949 width=208) (actual time=76.395..76.423 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=4.146..65.409 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=1.980..2.142 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=2.101..2.104 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)
-> Sort (cost=565372.46..568084.16 rows=1084680 width=74) (actual time=5410606.604..5410606.628 rows=31 loops=1)
Sort Key: a1.subj
Sort Method: quicksort Memory: 489474kB
-> Seq Scan on jena_g1t1_stmt a1 (cost=0.00..456639.59 rows=1084680 width=74) (actual time=0.043..44005.780 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: 5410691.012 ms
(18 rows)
--
Mohammad Farhan Husain
Research Assistant
Department of Computer Science
Erik Jonsson School of Engineering and Computer Science
University of Texas at Dallas
pgsql-performance by date: