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

Re: CLUSTER and a problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andrzej Zawadzki <zawadaa(at)wp(dot)pl>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: CLUSTER and a problem
Date: 2009-09-14 23:13:33
Message-ID: 21876.1252970013@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Andrzej Zawadzki <zawadaa(at)wp(dot)pl> writes:
> # EXPLAIN ANALYZE SElect telekredytid from kredytyag
> WHERE TRUE
> AND kredytyag.id = 3064776
> AND NOT EXISTS
> (SELECT 1 FROM
> ( SELECT * FROM kredyty kr
> where telekredytid = 328650
> ORDER BY kr.datazaw DESC LIMIT 1 )
> kred where kred.bank = 2);

So this is the slow bit:

>      ->  Subquery Scan kred  (cost=0.00..778.06 rows=1 width=0) (actual
> time=2045556.496..2045556.496 rows=0 loops=1)
>            Filter: (kred.bank = 2)
>            ->  Limit  (cost=0.00..778.05 rows=1 width=3873) (actual
> time=2045556.492..2045556.492 rows=0 loops=1)
>                  ->  Index Scan Backward using kredyty_datazaw on
> kredyty kr  (cost=0.00..1088490.39 rows=1399 width=3873) (actual
> time=2045556.487..2045556.487 rows=0 loops=1)
>                        Filter: (telekredytid = 328650)

It's doing a scan in descending datazaw order and hoping to find a row
that has both telekredytid = 328650 and bank = 2.  Evidently there isn't
one, so the indexscan runs clear to the end before it can report that the
NOT EXISTS is true.  Unfortunately, you've more or less forced this
inefficient query plan by wrapping some of the search conditions inside a
LIMIT and some outside.  Try phrasing the NOT EXISTS query differently.
Or, if you do this type of query a lot, a special-purpose index might be
worthwhile.  It would probably be fast as-is if you had an index on
(telekredytid, datazaw) (in that order).

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Michael GlaesemannDate: 2009-09-14 23:19:03
Subject: Re: How to post Performance Questions
Previous:From: C StormDate: 2009-09-14 21:48:17
Subject: noapic option

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