CLUSTER and a problem

From: Andrzej Zawadzki <zawadaa(at)wp(dot)pl>
To: pgsql-performance(at)postgresql(dot)org
Subject: CLUSTER and a problem
Date: 2009-09-14 14:19:02
Message-ID: 4AAE50D6.5020000@wp.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Robert Haas 2009-09-14 15:30:03 Re: possible wrong query plan on pg 8.3.5,
Previous Message zz_11 2009-09-14 14:17:15 Re: possible wrong query plan on pg 8.3.5,