From: | Dennis Björklund <db(at)zigo(dot)dhs(dot)org> |
---|---|
To: | Pedro Alves <pmalves(at)think(dot)pt> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostGreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Index usage |
Date: | 2003-08-26 15:02:56 |
Message-ID: | Pine.LNX.4.44.0308261655580.31606-100000@zigo.dhs.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeffrey Melloy | 2003-08-26 15:06:51 | Weird psql problem |
Previous Message | Dennis Gearon | 2003-08-26 15:02:39 | Re: many columns with references to one table |