indexing - creates problem
I am having a table with more than 1000 records, i am not having index in
that, while executing that query it occupies the processor..
I created an index, and then executed that query., Now it is not getting
executed at all... while seeing the top the processor is busy in WA, that it
is waiting for device....
i thought it is because of some process, and tested it again, and again and
found that the processor is busy with this device whenever this query is
executed...
i dropped the index and tried, now the query is executed but it takes...
any idea ????
I am having a table with more than 1000 records, i am not having index in
that, while executing that query it occupies the processor..
1000 rows is not much - I guess the index is not necessary at all, as the
traditional sequential scan is faster than index scan (due to random
access vs. sequential access).
But you have not provided enough information, so we can't give you precise
answer. You should answer at least these questions:
0) What version of postgresql (and on what OS) are you running? What
machine is it running on?
1) What is the structure of the table? What columns does have, etc. Post
the CREATE script, or a similar description.
2) What query are you executing? Post the query as well as an explain plan
for it (EXPLAIN command before the SELECT).
3) Have you analyzed the table before executing the query? Have you
vacuumed the table recently?
Tomas
now it is for 5000000 records.
postgres 7.4
Debian
----------------------
call_id | integer | not null default
nextval('call_log_seq'::text)
agent_id | integer |
----------------------------
call_id already has index.
count(*) gives output in 17 seconds.....
after creating index for agent_id it is not giving result for the same.
On Wed, Mar 5, 2008 at 9:45 PM, <tv@fuzzy.cz> wrote:
Show quoted text
I am having a table with more than 1000 records, i am not having index
in
that, while executing that query it occupies the processor..
1000 rows is not much - I guess the index is not necessary at all, as the
traditional sequential scan is faster than index scan (due to random
access vs. sequential access).But you have not provided enough information, so we can't give you precise
answer. You should answer at least these questions:0) What version of postgresql (and on what OS) are you running? What
machine is it running on?1) What is the structure of the table? What columns does have, etc. Post
the CREATE script, or a similar description.2) What query are you executing? Post the query as well as an explain plan
for it (EXPLAIN command before the SELECT).3) Have you analyzed the table before executing the query? Have you
vacuumed the table recently?Tomas