Thread

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

   Alexandr S. <say@infobus.ru> — 2001-06-15T06:23:43Z

   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.
   
   
   
   
   

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

   Andrew Snow <andrew@modulus.org> — 2001-06-15T06:58:47Z

   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
   >
   
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-15T14:10:45Z

   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