Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 3726F133655C
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  3 Oct 2010 18:40:04 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 10973-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  3 Oct 2010 21:39:56 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id A135B1336473
	for <pgsql-performance@postgresql.org>;
	Sun,  3 Oct 2010 18:39:56 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o93LdsUv006007;
	Sun, 3 Oct 2010 17:39:55 -0400 (EDT)
To: =?ISO-8859-1?Q?Fabr=EDcio_dos_Anjos_Silva?=
 <fabricio.silva@linkcom.com.br>
cc: pgsql-performance@postgresql.org
Subject: Re: Wrong index choice 
In-reply-to: <AANLkTimKtC1PLdyK6ToRHG-5GA_qmULtMvYQeFYpgtL+@mail.gmail.com> 
References: <AANLkTikvrMuHC=+AqBEBd0gW-ahiLQ2c-U2gdW9KuWse@mail.gmail.com>
	<AANLkTimKtC1PLdyK6ToRHG-5GA_qmULtMvYQeFYpgtL+@mail.gmail.com>
Comments: In-reply-to =?ISO-8859-1?Q?Fabr=EDcio_dos_Anjos_Silva?=
	<fabricio.silva@linkcom.com.br>
	message dated "Wed, 29 Sep 2010 13:55:26 -0300"
Date: Sun, 03 Oct 2010 17:39:54 -0400
Message-ID: <6006.1286141994@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201010/18
X-Sequence-Number: 40437

=?ISO-8859-1?Q?Fabr=EDcio_dos_Anjos_Silva?= <fabricio.silva@linkcom.com.br> writes:
> explain analyze select max(cnpj) from empresa where dtcriacao >=
> current_date-5;
>                                                                       QUERY
> PLAN
> -----------------------------------------------------------------------------------------
>  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
> (7 rows)

BTW, a large part of the reason that it's switching to this plan type
too soon is that you've got random_page_cost set really small:

> seq_page_cost = 0.01
> random_page_cost = 0.01

I think you need to back those off by an order of magnitude or so.

			regards, tom lane