Re: CLUSTER and a problem

Andrzej Zawadzki wrote:
> Tom Lane wrote:
>
>> Andrzej Zawadzki <zawadaa@wp.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 :-)
>
>
Before CLUSTER was:

# EXPLAIN ANALYZE SELECT telekredytid FROM kredytyag
WHERE TRUE
AND kredytyag.id = 3064776
AND NOT EXISTS
    (
    SELECT 1 FROM
        (
            SELECT * FROM kredyty kr
            where telekredytid = 328652
            ORDER BY kr.datazaw DESC LIMIT 1
    )
    kred where kred.bank = 2)
;

QUERY
PLAN


------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=1317.25..1325.55 rows=1 width=4) (actual
time=0.235..0.235 rows=0 loops=1)
   One-Time Filter: (NOT $0)
   InitPlan
     ->  Subquery Scan kred  (cost=1317.24..1317.25 rows=1 width=0)
(actual time=0.188..0.188 rows=0 loops=1)
           Filter: (kred.bank = 2)
           ->  Limit  (cost=1317.24..1317.24 rows=1 width=4006) (actual
time=0.172..0.172 rows=0 loops=1)
                 ->  Sort  (cost=1317.24..1320.27 rows=1212 width=4006)
(actual time=0.069..0.069 rows=0 loops=1)
                       Sort Key: kr.datazaw
                       Sort Method:  quicksort  Memory: 25kB
                       ->  Index Scan using kredyty_telekredytid_idx on
kredyty kr  (cost=0.00..1311.18 rows=1212 width=4006) (actual
time=0.029..0.029 rows=0 loops=1)
                             Index Cond: (telekredytid = 328652)
   ->  Index Scan using kredytyag_pkey on kredytyag  (cost=0.00..8.29
rows=1 width=4) (actual time=0.018..0.018 rows=0 loops=1)
         Index Cond: (id = 3064776)
 Total runtime: 1.026 ms
(14 rows)

and that's clear for me.
Probably bad index for CLUSTER - Investigating ;-)

--
Andrzej Zawadzki