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 :-)
>
OK I've got it :-)
I've prepared test database (on fast disks - CLUSTER took 2h anyway ;-)

Step 1:
qstest=# CREATE UNIQUE INDEX kredyty_desc_pkey ON kredyty using btree
(id desc);
CREATE
INDEX
Step 2:
qstest=# CLUSTER kredyty USING kredyty_desc_pkey;
CLUSTER
Step 3:
qstest=# ANALYZE kredyty;
ANALYZE
Step 4:
qstest=# 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=833.09..841.38 rows=1 width=4) (actual
time=70.050..70.050 rows=0
loops=1)

   One-Time Filter: (NOT
$0)
                    


InitPlan
                                          

     ->  Subquery Scan kred  (cost=833.07..833.09 rows=1 width=0)
(actual time=48.223..48.223 rows=0
loops=1)
           Filter: (kred.bank =
2)
             

           ->  Limit  (cost=833.07..833.08 rows=1 width=3975) (actual
time=48.206..48.206 rows=0
loops=1)
                 ->  Sort  (cost=833.07..835.66 rows=1035 width=3975)
(actual time=48.190..48.190 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..827.90 rows=1035 width=3975) (actual
time=48.163..48.163 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=21.798..21.798 rows=0 loops=1)
         Index Cond: (id = 3064776)
 Total runtime: 70.550 ms
(14 rows)

qstest=#

So, I was close - bad index... DESCending is much better.
Thanks to Grzegorz Ja\skiewicz  hi has strengthened me in the conjecture.

I'm posting this - maybe someone will find something useful in that case.

ps. query was and is good :-)

--
Andrzej Zawadzki