Re: Abnormal performance difference between Postgres and MySQL

[Farhan Husain <russoue@xxxxxxxxx>]

The result set should have 31 rows, that is correct.

On Mon, Feb 23, 2009 at 7:53 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:

On Mon, Feb 23, 2009 at 6:24 PM, Farhan Husain <russoue@xxxxxxxxx> 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.

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