Re: Sorting performance vs. MySQL

Op 22 feb 2010, om 20:07 heeft Yang Zhang het volgende geschreven:

> On Mon, Feb 22, 2010 at 1:48 PM, Alban Hertroys
> <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:
>> On 22 Feb 2010, at 19:35, Yang Zhang wrote:
>>
>>> I also wouldn't have imagined an external merge-sort as being very
>>
>>
>> Where's that external merge-sort coming from? Can you show an
>> explain analyze?
>
> I just assumed that the "Sort" in the EXPLAIN output meant an external
> merge-sort, given that the table has over 50 million tuples and is
> over 3GB, *and* there is no index on the sort key:
>
> tpcc=# explain select * from metarelcloud_transactionlog order by
> transactionid;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------
> Sort (cost=8408637.34..8534662.95 rows=50410244 width=17)
> Sort Key: a.transactionid
> -> Seq Scan on metarelcloud_transactionlog a
> (cost=0.00..925543.44 rows=50410244 width=17)
> (3 rows)
>
> Anyway, I added the INDEX as suggested by Frank, but it's been 20
> minutes and it's still running. With the index, EXPLAIN says:
>
> tpcc=# explain select * from metarelcloud_transactionlog order by
> transactionid;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------
> Index Scan using i_transactionid on metarelcloud_transactionlog
> (cost=0.00..4453076.81 rows=50410164 width=44)
> (1 row)
>

Use EXPLAIN ANALYZE to see how the query is executed, gives you more
details.

>> If your work-mem is too low there's a good chance that Postgres has
>> to use your disks for sorting, which will obviously be quite slow.
>
> Relative to the non-terminating 80-minute-so-far sort, Unix sort runs
> much faster (on the order of several minutes).

Make sure your index does fit into memory, what's the size of the index?

> --
> Yang Zhang
> http://www.mit.edu/~y_z/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Frank Heikens