Skip site navigation (1) Skip section navigation (2)

CLUSTER and a problem

From: Andrzej Zawadzki <zawadaa(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: CLUSTER and a problem
Date: 2009-09-14 13:54:14
Message-ID: 4AAE4B06.6010806@gmail.com (view raw or flat)
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

pgsql-performance by date

Next:From: zz_11Date: 2009-09-14 14:17:15
Subject: Re: possible wrong query plan on pg 8.3.5,
Previous:From: Robert HaasDate: 2009-09-14 13:38:22
Subject: Re: possible wrong query plan on pg 8.3.5,

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group