Re: Index usage

On Tue, Aug 26, 2003 at 10:25:11AM -0400, Tom Lane wrote:
> Pedro Alves <pmalves(at)think(dot)pt> writes:
> > It's the same even with statistics=1000.
>
> Uh, you did actually ANALYZE the table after each change, right?
> Doesn't the EXPLAIN output change at all?
>
> regards, tom lane

I can't see much of a difference. Here goes the details

alter table requisicaoanalise alter column ra_datacolh set statistics -1;
VACUUM ANALYZE requisicaoanalise;

explain analyse select count(1) from requisicaoanalise where isactive=0 and ra_datacolh >= '2003-2-01' and ra_datacolh < '2003-3-1';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10496.29..10496.29 rows=1 width=0) (actual time=633.52..633.52 rows=1 loops=1)
-> Seq Scan on requisicaoanalise (cost=0.00..10484.89 rows=4561 width=0) (actual time=127.77..627.76 rows=9032 loops=1)
Filter: ((isactive = 0) AND (ra_datacolh >= '2003-02-01'::date) AND (ra_datacolh < '2003-03-01'::date))
Total runtime: 633.61 msec
(4 rows)

alter table requisicaoanalise alter column ra_datacolh set statistics 1000;
VACUUM ANALYZE requisicaoanalise;

explain analyse select count(1) from requisicaoanalise where isactive=0 and ra_datacolh >= '2003-2-01' and ra_datacolh < '2003-3-1';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10507.43..10507.43 rows=1 width=0) (actual time=638.73..638.73 rows=1 loops=1)
-> Seq Scan on requisicaoanalise (cost=0.00..10484.89 rows=9015 width=0) (actual time=119.19..633.36 rows=9032 loops=1)
Filter: ((isactive = 0) AND (ra_datacolh >= '2003-02-01'::date) AND (ra_datacolh < '2003-03-01'::date))
Total runtime: 639.33 msec
(4 rows)

--
Pedro Miguel G. Alves pmalves(at)think(dot)pt
THINK - Tecnologias de Informação www.think.pt
Tel: +351 21 413 46 00 Av. José Gomes Ferreira
Fax: +351 21 413 46 09 nº 13 1495-139 ALGÉS