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

Re: Abnormal performance difference between Postgres and MySQL

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Farhan Husain <russoue(at)gmail(dot)com>
Cc: Gregory Stark <stark(at)enterprisedb(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Abnormal performance difference between Postgres and MySQL
Date: 2009-02-24 01:53:50
Message-ID: dcc563d10902231753rc8e9b53l5b918454500dd1af@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Feb 23, 2009 at 6:24 PM, Farhan Husain <russoue(at)gmail(dot)com> wrote:
This sort here:

>    ->  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)

Seems to be the problem.  There are a few things that seem odd, the
first is that it estimates it will return 1M ros, but returns only 31.
 The other is that sorting 31 rows is taking 5410606 milliseconds.

My first guess would be to crank up the statistics on a1.subj to a few
hundred (going up to a thousand if necessary) re-analyzing and seeing
if the query plan changes.

I'm not expert enough on explain analyze to offer any more.

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-02-24 02:00:58
Subject: Re: Abnormal performance difference between Postgres and MySQL
Previous:From: Farhan HusainDate: 2009-02-24 01:24:49
Subject: Re: Abnormal performance difference between Postgres and MySQL

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