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