Thread: the index on INTEGER field does not work (PG 7.1.2)

Bug: the index on INTEGER field does not work (PG 7.1.2).

Test 1:

1) create table test_int(id int primary key);

2) insert 10000 records in table test_int with perl program (values
1,2,3,...,10000).

3) 500 times execute query

   SELECT * FROM test_int WHERE id = random number

   (random number puts with perl program)

4) 500 queries executes for 30 seconds

Test 2:

1) create table test_int1(id int); (i.e. without primary key)

2) insert 10000 records in table test_int1 with perl program (values
1,2,3,...,10000).

3) 500 times execute query

   SELECT * FROM test_int1 WHERE id = random number

   (random number puts with perl program)

4) 500 queries executes for 30 seconds too  :~-( ...


P.S. For TEXT field the same operations executes for 30 and 1 seconds
correspondingly.

Please see FAQ 4.9


http://postgresql.bteg.net/docs/faq-english.html#4.9



On Fri, 15 Jun 2001, Alexandr S. wrote:

>
> Bug: the index on INTEGER field does not work (PG 7.1.2).
>
> Test 1:
>
> 1) create table test_int(id int primary key);
>
> 2) insert 10000 records in table test_int with perl program (values
> 1,2,3,...,10000).
>
> 3) 500 times execute query
>
>    SELECT * FROM test_int WHERE id = random number
>
>    (random number puts with perl program)
>
> 4) 500 queries executes for 30 seconds
>
> Test 2:
>
> 1) create table test_int1(id int); (i.e. without primary key)
>
> 2) insert 10000 records in table test_int1 with perl program (values
> 1,2,3,...,10000).
>
> 3) 500 times execute query
>
>    SELECT * FROM test_int1 WHERE id = random number
>
>    (random number puts with perl program)
>
> 4) 500 queries executes for 30 seconds too  :~-( ...
>
>
> P.S. For TEXT field the same operations executes for 30 and 1 seconds
> correspondingly.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Andrew Snow <andrew@modulus.org> writes:
> Please see FAQ 4.9

While that's a good answer in general, the default statistics are set up
(with malice aforethought) to give index scans.

regression=# create table test_int(id int primary key);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'test_int_pkey' for table 'test_int'
CREATE
regression=# explain SELECT * FROM test_int WHERE id = 33;
NOTICE:  QUERY PLAN:

Index Scan using test_int_pkey on test_int  (cost=0.00..8.14 rows=10 width=4)

EXPLAIN

So I'm not sure what's going on here.  Alexandr, what do you get from
EXPLAIN for your queries?

            regards, tom lane