Hi!
Yesterday I Clustered one big table (# CLUSTER kredyty USING kredyty_pkey;)
and today one query is extremely slow.
query:
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)
Plan looks strange for me:
"Result (cost=701.54..709.84 rows=1 width=4)"
" One-Time Filter: (NOT $0)"
" InitPlan"
" -> Subquery Scan kred (cost=0.00..701.54 rows=1 width=0)"
" Filter: (kred.bank = 2)"
" -> Limit (cost=0.00..701.52 rows=1 width=3902)"
" -> Index Scan Backward using kredyty_datazaw on
kredyty kr (cost=0.00..1067719.61 rows=1522 width=3902)"
" Filter: (telekredytid = 328652)"
" -> Index Scan using kredytyag_pkey on kredytyag (cost=0.00..8.30
rows=1 width=4)"
" Index Cond: (id = 3064776)"
This Index skan on kredyty_datazaw and filter telekredytid cost a lot
of... but why not use kredyty_telekredytid_idx?
Before Cluster was (or similar):
"Result (cost=78.98..85.28 rows=1 width=4)"
" One-Time Filter: (NOT $0)"
" InitPlan 1 (returns $0)"
" -> Subquery Scan kred (cost=78.97..78.98 rows=1 width=0)"
" Filter: (kred.bank = 2)"
" -> Limit (cost=78.97..78.97 rows=1 width=3910)"
" -> Sort (cost=78.97..79.20 rows=94 width=3910)"
" Sort Key: kr.datazaw"
" -> Index Scan using kredyty_telekredytid_idx on
kredyty kr (cost=0.00..78.50 rows=94 width=3910)"
" Index Cond: (telekredytid = 328652)"
" -> Index Scan using kredytyag_pkey on kredytyag (cost=0.00..6.30
rows=1 width=4)"
" Index Cond: (id = 3064776)"
I've chosen bad index?
--
Andrzej Zawadzki
