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
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, |