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

Re: CLUSTER and a problem

From: Andrzej Zawadzki <zawadaa(at)wp(dot)pl>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: CLUSTER and a problem
Date: 2009-09-15 07:36:38
Message-ID: 4AAF4406.3010307@wp.pl (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> Andrzej Zawadzki <zawadaa(at)wp(dot)pl> writes:
>   
>> # EXPLAIN ANALYZE SElect telekredytid from kredytyag
>> WHERE TRUE
>> AND kredytyag.id = 3064776
>> AND NOT EXISTS
>> (SELECT 1 FROM
>> ( SELECT * FROM kredyty kr
>> where telekredytid = 328650
>> ORDER BY kr.datazaw DESC LIMIT 1 )
>> kred where kred.bank = 2);
>>     
>
> So this is the slow bit:
>
>   
>>      ->  Subquery Scan kred  (cost=0.00..778.06 rows=1 width=0) (actual
>> time=2045556.496..2045556.496 rows=0 loops=1)
>>            Filter: (kred.bank = 2)
>>            ->  Limit  (cost=0.00..778.05 rows=1 width=3873) (actual
>> time=2045556.492..2045556.492 rows=0 loops=1)
>>                  ->  Index Scan Backward using kredyty_datazaw on
>> kredyty kr  (cost=0.00..1088490.39 rows=1399 width=3873) (actual
>> time=2045556.487..2045556.487 rows=0 loops=1)
>>                        Filter: (telekredytid = 328650)
>>     
>
> It's doing a scan in descending datazaw order and hoping to find a row
> that has both telekredytid = 328650 and bank = 2.  Evidently there isn't
> one, so the indexscan runs clear to the end before it can report that the
> NOT EXISTS is true.  Unfortunately, you've more or less forced this
> inefficient query plan by wrapping some of the search conditions inside a
> LIMIT and some outside.  Try phrasing the NOT EXISTS query differently.
> Or, if you do this type of query a lot, a special-purpose index might be
> worthwhile.  It would probably be fast as-is if you had an index on
> (telekredytid, datazaw) (in that order).
>   
That's no problem - we already has changed this query:
SELECT * FROM kredyty kr
            where kr.telekredytid = 328652
            and kr.bank = 2
            AND NOT EXISTS (SELECT * from kredyty k2 WHERE k2.bank<>2
and k2.creationdate > kr.creationdate)
Works good.

But in fact this wasn't my point.
My point was: why operation CLUSTER has such a big and bad impact on
planer for this query?
Like I sad: before CLUSTER query was run in xx milliseconds :-)

-- 
Andrzej Zawadzki

In response to

Responses

pgsql-performance by date

Next:From: Ludwik DylagDate: 2009-09-15 08:06:21
Subject: disable heavily updated (but small) table auto-vecuuming
Previous:From: Віталій ТимчишинDate: 2009-09-15 06:32:26
Subject: Re: possible wrong query plan on pg 8.3.5,

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