Index usage

[Pedro Alves]

Hi. I'm having some trouble on the use of indexes.

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?

Query 2 hash 9105 entries matching the given conditions
Query 2 hash 9248 entries matching the given conditions

QUERY 1:

explain select ra_datacolh::date, count(distinct ra_utente) from requisicaoanalise where (ra_datacolh::date >= '2003-4-01'::date and ra_datacolh::date < '2003-5-1'::date) and isactive=0 group by ra_datacolh;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=0.00..12300.68 rows=313 width=8)
-> Group (cost=0.00..12292.86 rows=3130 width=8)
-> Index Scan using requisicaoanalise_datacolh on requisicaoanalise (cost=0.00..12285.03 rows=3130 width=8)
Index Cond: ((ra_datacolh >= '2003-04-01'::date) AND (ra_datacolh < '2003-05-01'::date))
Filter: (isactive = 0)

QUERY 2:

netlab=> explain select ra_datacolh::date, count(distinct ra_utente) from requisicaoanalise where (ra_datacolh::date >= '2003-6-01'::date and ra_datacolh::date < '2003-7-1'::date) and isactive=0 group by ra_datacolh;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=32452.98..32632.06 rows=2388 width=8)
-> Group (cost=32452.98..32572.36 rows=23877 width=8)
-> Sort (cost=32452.98..32512.67 rows=23877 width=8)
Sort Key: ra_datacolh
-> Seq Scan on requisicaoanalise (cost=0.00..30716.71 rows=23877 width=8)
Filter: ((ra_datacolh >= '2003-06-01'::date) AND (ra_datacolh < '2003-07-01'::date) AND (isactive = 0))


--
Pedro Miguel G. Alves pm*****@think.pt
THINK - Tecnologias de Informação www.think.pt
Tel: [phone] Av. José Gomes Ferreira
Fax: [phone] nº 13 1495-139 ALGÉS

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Dennis Björklund]

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.

--
/Dennis
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Pedro Alves]

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) (actual time=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 ma*******@postgresql.org

--
Pedro Miguel G. Alves pm*****@think.pt
THINK - Tecnologias de Informação www.think.pt
Tel: [phone] Av. José Gomes Ferreira
Fax: [phone] nº 13 1495-139 ALGÉS

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Pedro Alves <pm*****@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Pedro Alves <pm*****@think.pt> writes:

The vacuum analyze is run on daily basis, so that cannot be the point.

The next thing I'd try to improve the planner's guess is to increase the
statistics target for the ra_datacolh column (see ALTER TABLE SET
STATISTICS). It looks like the default of 10 is too small for that
column --- try 100 and see if the rowcount estimates get any closer.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Martijn van Oosterhout]

Look through the docs. By altering the values of random_page_cost,
effect_cache_size and cpu_tuple_cost you can make the estimates approximate
real life better.

On Tue, Aug 26, 2003 at 12:32:23PM +0100, Pedro Alves wrote:

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 ina
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) (actual time=0.13..145.50rows=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 ma*******@postgresql.org

--
Pedro Miguel G. Alves pm*****@think.pt
THINK - Tecnologias de Informação www.think.pt
Tel: [phone] Av. José Gomes Ferreira
Fax: [phone] nº 13 1495-139 ALGÉS

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ "All that is needed for the forces of evil to triumph is for enough good
men to do nothing." - Edmond Burke
"The penalty good people pay for not being interested in politics is to be
governed by people worse than themselves." - Plato

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/S1rQY5Twig3Ge+YRAqv4AKC+oQx6Xz1f/SDQcaYyfLCHwID1qQCeLn/o
6gf9kpEGHVblz1Qjgnc3/+0=
=mXMg
-----END PGP SIGNATURE-----