On Fri, 19 Mar 2010, Stephen Frost wrote:
> ...it has to go to an external on-disk sort (see later on, and how to
> fix that).
This was covered on this list a few months ago, in
There seemed to be some consensus that allowing a materialise in front of
an index scan might have been a good change. Was there any movement on
>> "Limit (cost=66681.50..66681.50 rows=1 width=139) (actual
>> time=7413.489..7413.489 rows=1 loops=1)"
>> " -> Merge Anti Join (cost=40520.17..66681.50 rows=367793 width=139)
>> (actual time=3705.078..7344.256 rows=1000001 loops=1)"
>> " Merge Cond: ((f1.user_id = f2.ref_id) AND (f1.ref_id =
>> " -> Index Scan using user_ref on friends f1
>> (cost=0.00..26097.86 rows=2818347 width=139) (actual
>> time=0.093..1222.592 rows=1917360 loops=1)"
>> " -> Materialize (cost=40520.17..40555.40 rows=2818347 width=8)
>> (actual time=3704.977..5043.347 rows=1990148 loops=1)"
>> " -> Sort (cost=40520.17..40527.21 rows=2818347 width=8)
>> (actual time=3704.970..4710.703 rows=1990148 loops=1)"
>> " Sort Key: f2.ref_id, f2.user_id"
>> " Sort Method: external merge Disk: 49576kB"
>> " -> Seq Scan on friends f2 (cost=0.00..18143.18
>> rows=2818347 width=8) (actual time=0.015..508.797 rows=2818347 loops=1)"
>> "Total runtime: 7422.516 ms"
> If you had an index on ref_id,user_id (as well as the one on
> user_id,ref_id), it'd probably be able to do in-order index traversals
> on both and be really fast... But then updates would be more expensive,
> of course, since it'd have more indexes to maintain.
That isn't necessarily so, until the issue referred to in the above linked
messages is resolved. It depends.
I've run DOOM more in the last few days than I have the last few
months. I just love debugging ;-) -- Linus Torvalds
In response to
pgsql-performance by date
|Next:||From: Tom Lane||Date: 2010-03-22 12:40:12|
|Subject: Re: too complex query plan for not exists query and multicolumn indexes |
|Previous:||From: Pierre C||Date: 2010-03-22 11:15:51|
|Subject: Re: mysql to postgresql, performance questions|