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: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Andrzej Zawadzki <zawadaa(at)wp(dot)pl> writes:
> # EXPLAIN ANALYZE SElect telekredytid from kredytyag
> AND = 3064776
> ( SELECT * FROM kredyty kr
> where telekredytid = 328650
> ORDER BY kr.datazaw DESC LIMIT 1 )
> kred where = 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: ( = 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


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-2017 The PostgreSQL Global Development Group