Thread: CLUSTER and a problem
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