index not being used
I'm having problem where index is not being used in query on very big
table (10 million rows), even after I set the enable_seqscan=off and
enable_indexscan=on. The query always choose seq scan when I do EXPLAIN.
This causes the query to take about 25 seconds to execute.
However, if the table only has 1 million rows, the query chooses the
index scan and only take about 80 msec.
here is the table structure:
create table test_10million (
id int8,
app_id int8
);
< fill the table with 10 million record >
create index test_10million_id on test_10million (id);
this query always uses sequential scan:
select * from test_10million where id = 123 and app_id = 100;
Does the number of rows matter here? (is 10 million too big for the
index table?).
thanks,
- reynard
You're missing an analyze step: (see below)
here is the table structure:
create table test_10million (
id int8,
app_id int8
);< fill the table with 10 million record >
create index test_10million_id on test_10million (id);
ANALYZE test_10million;
this query always uses sequential scan:
select * from test_10million where id = 123 and app_id = 100;
Now try
explain select * from test_10million where id = 123 and app_id = 100;
Cheers,
Doug
On Sat, 14 Dec 2002, Doug Fields wrote:
You're missing an analyze step: (see below)
here is the table structure:
create table test_10million (
id int8,
app_id int8
);< fill the table with 10 million record >
create index test_10million_id on test_10million (id);
ANALYZE test_10million;
this query always uses sequential scan:
select * from test_10million where id = 123 and app_id = 100;Now try
explain select * from test_10million where id = 123 and app_id = 100;
Not forgetting of course that the numbers used in the tests will probably need
to be cast to int8 or quoted to make into text constants before the index is
used. I'm surprised the 1 million row test used the index. Unless this is in
7.3 and the behaviour has changed (which I can't remember about).
--
Nigel J. Andrews
you're right about the int8 Nigel,
select * from test_10million where id = 123::int8 and app_id = 100;
does solve the problem (only takes 2.88 msec).
I forgot to mention that I use different table for the 1 million records, and it does use int4, so that explains why index works for that table.
thanks,
- reynard
Nigel J. Andrews wrote:
Show quoted text
On Sat, 14 Dec 2002, Doug Fields wrote:
You're missing an analyze step: (see below)
here is the table structure:
create table test_10million (
id int8,
app_id int8
);< fill the table with 10 million record >
create index test_10million_id on test_10million (id);
ANALYZE test_10million;
this query always uses sequential scan:
select * from test_10million where id = 123 and app_id = 100;Now try
explain select * from test_10million where id = 123 and app_id = 100
Not forgetting of course that the numbers used in the tests will probably need
to be cast to int8 or quoted to make into text constants before the index is
used. I'm surprised the 1 million row test used the index. Unless this is in
7.3 and the behaviour has changed (which I can't remember about).