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 http://archives.postgresql.org/pgsql-performance/2009-08/msg00184.php and http://archives.postgresql.org/pgsql-performance/2009-08/msg00189.php
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 this front?
"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 = f2.user_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.
Matthew -- I've run DOOM more in the last few days than I have the last few months. I just love debugging ;-) -- Linus Torvalds -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
