Re: Index usage

On Tue, Aug 26, 2003 at 10:25:11AM -0400, Tom Lane wrote:
> Pedro Alves <pmalves@think.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@think.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