From: postgres@ied.com
To: pgsql-sql@postgresql.org
Subject: switching default integer datatype to int8 and "IN (...)" clause
Date: Thu, 9 Jan 2003 04:24:12 -0500 (EST)
Message-ID: <no.Yo.N.nN.0301090355170.2533-100000@business.com> (raw)
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgres@ied.com
Subject: Re: switching default integer datatype to int8 and "IN (...)" clause
In-Reply-To: <no.Yo.N.nN.0301090355170.2533-100000@business.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox