473,224 Members | 1,401 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

Index usage



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: +351 21 413 46 00 Av. José Gomes Ferreira
Fax: +351 21 413 46 09 nº 13 1495-139 ALGÉS

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

Nov 11 '05 #1
5 1847
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)

Nov 11 '05 #2

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: +351 21 413 46 00 Av. José Gomes Ferreira
Fax: +351 21 413 46 09 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

Nov 11 '05 #3
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

Nov 11 '05 #4
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

Nov 11 '05 #5
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: +351 21 413 46 00 Av. José Gomes Ferreira
Fax: +351 21 413 46 09 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-----

Nov 11 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Andrew Dalke | last post by:
Is there an author index for the new version of the Python cookbook? As a contributor I got my comp version delivered today and my ego wanted some gratification. I couldn't find my entries. ...
6
by: Heiko | last post by:
Hello, is there any way (v$-view) to get informaion about how often an index hast been used since of starting the Database? Thanks for help Heiko
0
by: Guy Deprez | last post by:
Hi, i'm having a problem to create indexes. STEP 1 ----------- Connection is OK (you can find the string at the end of the message) Table ("Couleurs") creation is OK STEP 2. Index Creation
8
by: Andr? Queiroz | last post by:
Hi, I have a table with 10M records and col A has a index created on it. The data on that table has the same value for col A on all 10M records. After that I insert diferent values for that column...
8
by: Mike | last post by:
Hello, I have a few rather urgent questions that I hope someone can help with (I need to figure this out prior to a meeting tomorrow.) First, a bit of background: The company I work for is...
2
by: SKC | last post by:
Is there a way to find the following in UDB ? 1. How many times an index is used for a duration ? 2. Which SQL/application is using the index? if not, is there a different way to find it ?
12
by: comp.lang.php | last post by:
index.php: // STUFF // STEP 1: imagecreatetruecolor ONLY IF GD 2.0+ SUPPORTED AND FOUND if ($this->isSuccessful && !$hasMogrified && $image && !$newImage &&...
35
by: erikwickstrom | last post by:
Hi all, I'm sorry about the newbie question, but I've been searching all afternoon and can't find the answer! I'm trying to get this bit of code to work without triggering the IndexError. ...
13
by: Eugene Rice | last post by:
I'm writing C code for an Atmel AVR micro controller. Because RAM space is extremely limited (about 500 bytes) I use char variables wherever I can. For example I use chars as array indices in...
7
by: Henry J. | last post by:
I got a dumb question on the merge statement. I read the following example of merge statement at the IBM page:...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.