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