Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 0E28747595A
	for <pgsql-sql@postgresql.org>; Thu,  9 Jan 2003 04:24:17 -0500 (EST)
Received: from karel.ied.com (xtreme10-98.aci.on.ca [209.50.81.98])
	by postgresql.org (Postfix) with ESMTP id 082414758C9
	for <pgsql-sql@postgresql.org>; Thu,  9 Jan 2003 04:24:13 -0500 (EST)
Received: from [127.0.0.2] (step.ied.com [192.168.200.26])
	by karel.ied.com (8.12.6/8.11.6) with ESMTP id h099NG5V009372
	for <pgsql-sql@postgresql.org>; Thu, 9 Jan 2003 04:23:17 -0500
Date: Thu, 9 Jan 2003 04:24:12 -0500 (EST)
From: postgres@ied.com
To: pgsql-sql@postgresql.org
Subject: switching default integer datatype to int8 and "IN (...)" clause
Message-ID: <no.Yo.N.nN.0301090355170.2533-100000@business.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/117
X-Sequence-Number: 10982



 Hi,

 Short: postrgesql-7.2.3 doesn't figure to convert int4 to int8 
implicitly / by context (as in a query). How do I help it ?

 Long:
  I have index on a table, a select like this takes a split second:
# select * from file where id = fileIDseq.last_value-1;
   id   | name  
   -----+----------------
1921777 |  icons 

 but a select like this takes ages (looooong time):
# select * from file where id = 1921773;
   id   | name  
   -----+----------------
1921777 |  icons 

 but a select like this is quick again:
# select * from file where id = int8(1921773);
   id   | name  
   -----+----------------
1921777 |  icons 

 the secret seems to be that 
# explain select * from file where id = fileIDseq.last_value-1;
Nested Loop  (cost=0.00..6.04 rows=1 width=1359)
  ->  Seq Scan on fileidseq  (cost=0.00..1.01 rows=1 width=8)
  ->  Index Scan using file_pkey on file  (cost=0.00..5.02 rows=1 width=1351)

 whereas

# explain select * from file where id = 1921773;
Seq Scan on file  (cost=0.00..58905.95 rows=1 width=1351)

 The reason seems to be that fileIDseq.last_value-1 is type bigint
 (int8), whereas "1921773" is of type integer (int4).

 Now 
# explain select * from file where id in (fileIDseq.last_value-1,fileIDseq.last_value-1);
Nested Loop  (cost=0.00..6.04 rows=1 width=1359)
  ->  Seq Scan on fileidseq  (cost=0.00..1.01 rows=1 width=8)
  ->  Index Scan using file_pkey on file  (cost=0.00..5.02 rows=1 width=1351)
 BUT
# explain select * from file where id in (fileIDseq.last_value-1,fileIDseq.last_value-333); -- "-333" instead of same "-1"
Nested Loop  (cost=0.00..92278.69 rows=2 width=1359)
  ->  Seq Scan on fileidseq  (cost=0.00..1.01 rows=1 width=8)
  ->  Seq Scan on file  (cost=0.00..54138.56 rows=1906956 width=1351)


 Why ?

 Also, how do I tell postgresql that it should by default interpret
integers as "int8"s, and not as "int4"s ? (So that I don't have to
keep saying "int8(XYZ)" as in "select * from file where id = int8(1);"


    Thanks,

          John






mydb# \d file
                                     Table "file"
  Column  |            Type             |                  Modifiers
----------+-----------------------------+---------------------------------------------
 id       | bigint                      | not null default nextval('fileIDseq'::text)
 name     | character varying(255)      |
Primary key: file_pkey

mydb=# \d file_pkey
Index "file_pkey"
 Column |  Type
--------+--------
 id     | bigint
unique btree (primary key)

mydb==# \d fileidseq
  Sequence "fileidseq"
    Column     |  Type
---------------+---------
 sequence_name | name
 last_value    | bigint
 increment_by  | bigint
 max_value     | bigint
 min_value     | bigint
 cache_value   | bigint
 log_cnt       | bigint
 is_cycled     | boolean
 is_called     | boolean


-- 
-- Gospel of Jesus' kingdom = saving power of God for all who believe --
                 ## To some, nothing is impossible. ##
                       http://Honza.Vicherek.com/