Re: too complex query plan for not exists query and multicolumn indexes

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