Skip site navigation (1) Skip section navigation (2)

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

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: Corin <wakathane(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: too complex query plan for not exists query and multicolumn indexes
Date: 2010-03-22 11:48:44
Message-ID: alpine.DEB.2.00.1003221100080.9798@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2010-03-22 12:40:12
Subject: Re: too complex query plan for not exists query and multicolumn indexes
Previous:From: Pierre CDate: 2010-03-22 11:15:51
Subject: Re: mysql to postgresql, performance questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group