Re: Abnormal performance difference between Postgres and MySQL

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.