Re: Wrong index choice

=?ISO-8859-1?Q?Fabr=EDcio_dos_Anjos_Silva?= <fabricio(dot)silva(at)linkcom(dot)com(dot)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