Обсуждение: 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
indexworks for that table.  

thanks,
- reynard


Nigel J. Andrews wrote:

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