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

Re: Wrong index choice

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: Fabrício dos Anjos Silva <fabricio(dot)silva(at)linkcom(dot)com(dot)br>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Wrong index choice
Date: 2010-09-29 21:00:39
Message-ID: 4CA362A70200002500036136@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
Fabrício dos Anjos Silva<fabricio(dot)silva(at)linkcom(dot)com(dot)br> wrote:
 
> explain analyze select max(cnpj) from empresa where dtcriacao >=
> current_date-5;
 
>  Result  (cost=32.24..32.24 rows=1 width=0) (actual
> time=5223.937..5223.938 rows=1 loops=1)
>    InitPlan 1 (returns $0)
>      ->  Limit  (cost=0.00..32.24 rows=1 width=15) (actual
> time=5223.921..5223.922 rows=1 loops=1)
>            ->  Index Scan Backward using idx_cnpj on empresa
> (cost=0.00..65925.02 rows=2045 width=15) (actual
> time=5223.913..5223.913 rows=1 loops=1)
>                  Index Cond: ((cnpj)::text IS NOT NULL)
>                  Filter: (dtcriacao >= (('now'::text)::date - 5))
>  Total runtime: 5224.037 ms
 
> My question is: Why the cost of Limit on the last query, estimated
> as 32.24 if the Index Scan Backward is estimated at 65925.02?
 
If you divide the total cost for the step by the number of rows it
would take to read all the way through, you get 32.24; so it clearly
expects to find a row which matches the filter condition right away.
(Or it fails to consider the fact that the filter condition could
cause it to read multiple rows looking for a match.)
 
> Since there is a filter based on column dtcriacao, the whole index
> is going to be analyzed, and Limit is going to wait for the
> complete Index Scan to complete.
 
Only if there are no matching rows.  Since you're asking for the
max, if it reads in descending sequence on the index, it can stop as
soon as it finds one matching row.
 
-Kevin

In response to

pgsql-performance by date

Next:From: AndyDate: 2010-09-30 00:01:55
Subject: Performance improvements/regressions from 8.4 to 9.0?
Previous:From: Samuel GendlerDate: 2010-09-29 17:08:39
Subject: Re: How does PG know if data is in memory?

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