Re: Index usage and wrong cost analisys - Mailing list pgsql-general

    One more thing I just noticed. Right after making a vacuum analyze on
the table, I saw the following:

    Seq scan:

        - Cost: 10484
        - Timing: 624ms

    Index scan (with enable_seqscan = false):

        - Cost: 10628
        - Timing: 41ms


    In production state the query goes up to a minute long (I ran this in a
test database) and it takes less than a second using indexes. What can be
causing this?


    Is it safe to turn enable_seqscan = false in production environment?



    Thanks



On Tue, Aug 26, 2003 at 09:59:35AM +0100, Pedro Alves wrote:
>
> First of all tkx for the answer, Dennis.
>
> The vacuum analyze is run on daily basis, so that cannot be the point.
>
> Is there any way to force the planner to use the index?
>
>
> Bellow is the explain analyze of the querys. Indeed, the second range has
> more rows (9105 / 21503), but is this SO big that the planner cannot
> handle?
>
>
> This is running in a dedicated machine with 512Mb ram. Is there any
> configuration parameter so that I can increase the "index to seq turn point"? :)
>
>
> Thanks
>
>
> OK __________________________________
>
> explain ANALYZE select count(1) from requisicaoanalise where
> (ra_datacolh::date >= '2003-4-01'::date and ra_datacolh::date <
> '2003-5-1'::date) and isactive=0;
>
>  Aggregate  (cost=10660.84..10660.84 rows=1 width=0) (actual
> time=172.41..172.41 rows=1 loops=1)
>    ->  Index Scan using requisicaoanalise_datacolh on requisicaoanalise  (cost=0.00..10654.06 rows=2711 width=0)
(actualtime=0.13..145.50 rows=9105 loops=1) 
>          Index Cond: ((ra_datacolh >= '2003-04-01'::date) AND (ra_datacolh
> < '2003-05-01'::date))
>          Filter: (isactive = 0)
>  Total runtime: 172.62 msec
> (5 rows)
>
>
>
> NOK __________________________________
>
> explain ANALYZE select count(1) from requisicaoanalise where  (ra_datacolh::date >= '2003-6-01'::date and
ra_datacolh::date< '2003-7-1'::date) and isactive=0; 
>
> Aggregate  (cost=31019.00..31019.00 rows=1 width=0) (actual time=43252.40..43252.40 rows=1 loops=1)
>    ->  Seq Scan on requisicaoanalise  (cost=0.00..30965.24 rows=21503 width=0) (actual time=8.43..43224.01 rows=9248
loops=1)
>          Filter: ((ra_datacolh >= '2003-06-01'::date) AND (ra_datacolh < '2003-07-01'::date) AND (isactive = 0))
>  Total runtime: 43252.57 msec
> (4 rows)
>
>
>
>
>
> On Tue, Aug 26, 2003 at 08:37:08AM +0200, Dennis Björklund wrote:
> > On Mon, 25 Aug 2003, Pedro Alves wrote:
> >
> > >     The querys below are exactly the same but refer to different months.
> > > One case uses indexes, the other doesn't.
> > >
> > >     Is there anything I can do? Increasing index mem size?
> >
> > Run "vacuum analyze". The planner seems to think that one of the queries
> > returns 313 rows while the other returns 2388 rows.
> >
> > To me that looks like the statistics need to be updated using vacuum
> > analyze.
> >
> > Also, explain analyze gives a little more information and is better to
> > run then just explain.
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
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