Re: Index usage

On Tue, 26 Aug 2003, Pedro Alves wrote:

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

But there is a difference.

> alter table requisicaoanalise alter column ra_datacolh set statistics -1;
>
>  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))

Here the estimated row count is 4561 which is only half of the real count.

> alter table requisicaoanalise alter column ra_datacolh set statistics 1000;
>
>  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))

Here the estimate is 9015 which is very near the real count of 9032.

I would say that this estimate is much better. And before the other case
gave you an estimate of 20000 something, I bet that is also near the real
value of 9000 now.

What you need to do now is to try to lower RANDOM_PAGE_COST, and the other
settings like CPU_TUPLE_COST you can also play with.

--
/Dennis