Re: CLUSTER and a problem

Josh Berkus wrote:
> Andrzej,
>
> Please post a table & index schema, and an EXPLAIN ANALYZE rather than
> just an EXPLAIN.  Thanks!
>
EXPLAIN ANALYZE is taking too much time ;-) but now database is free so:

# 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);

QUERY PLAN


----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------
 Result  (cost=778.06..786.36 rows=1 width=4) (actual
time=2045567.930..2045567.930 rows=0 loops=1)
   One-Time Filter: (NOT $0)
   InitPlan
     ->  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)
   ->  Index Scan using kredytyag_pkey on kredytyag  (cost=0.00..8.30
rows=1 width=4) (actual time=11.424..11.424 rows=0 loops=1)
         Index Cond: (id = 3064776)
 Total runtime: 2045568.420 ms
(11 rows)

Like you can see below - disks are very busy

# sar -d -p
21:36:01          DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz
avgqu-sz     await     svctm     %util
21:38:01          sdd    219.58   3345.82    790.14     18.84
1.10      5.01      4.52     99.20

# vmstat 1
procs -----------memory---------- ---swap-- -----io---- -system--
----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy
id wa
 0  1   3976  93696  58452 14737524    1    1   455    84    0    0  8
1 90  2
 0  1   3976 106532  58384 14723812    0    0  1792     0  545  906  0
0 87 12
 0  1   3976 105452  58488 14725536    0    0  1708  2297  596  549  0
0 87 12
 0  1   3976 102924  58492 14727568    0    0  1996     0  554  566  0
0 87 12
 0  1   3976 102268  58492 14729028    0    0  1744     0  528  540  0
0 87 12
 0  1   3976  99828  58492 14730936    0    0  1624     0  507  492  0
0 87 12
 1  0   3976  98972  58492 14732688    0    0  1720     0  518  507  0
0 87 12
 0  1   3976  96756  58560 14734276    0    0  1636  2020  557  521  0
0 87 12


SCHEMA: this is big table (too big ;-) too wide ~250 columns so I've
trimmed schema - (old database without refactor :-( )
I hope this is enough?

                                                       Table
"public.kredyty"
                Column                 |            Type
|                          Modifiers

---------------------------------------+-----------------------------+--------------------------------------------------------------
 id                                    | integer                     |
not null default nextval(('kredyty_id_seq'::text)::regclass)
 linia                                 | integer                     |
default (-1)
 sklep                                 | integer                     |
default (-1)
 agent                                 | integer                     |
default (-1)
 przedst                               | integer                     |
default (-1)
 oddzial                               | integer                     |
default (-1)
 datazaw                               | date                        |
 datauruch                             | date                        |
 telekredytid                          | integer                     |
default (-1)
Indexes:
    "kredyty_pkey" PRIMARY KEY, btree (id) CLUSTER
    "kredyty_kredytagid_id_idx" UNIQUE, btree (kredytagid, id)
    "kredyty_datazaw" btree (datazaw)
    "kredyty_telekredytid_idx" btree (telekredytid)


--
Andrzej Zawadzki