switching default integer datatype to int8 and "IN (...)" clause

Поиск
Список
Период
Сортировка
Искать
От
postgres@ied.com
Тема
switching default integer datatype to int8 and "IN (...)" clause
Дата
Msg-id
no.Yo.N.nN.0301090355170.2533-100000@business.com
Список
Дерево обсуждения
switching default integer datatype to int8 and "IN (...)" clause postgres@ied.com
Re: switching default integer datatype to int8 and "IN (...)" clause "Andrew J. Kopciuch" <akopciuch@bddf.ca>
Re: switching default integer datatype to int8 and "IN (...)" postgres@ied.com

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 | namelast_value    | bigintincrement_by  | bigintmax_value     | bigintmin_value     | bigintcache_value   | bigintlog_cnt       | bigintis_cycled     | booleanis_called     | boolean


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



В списке pgsql-sql по дате отправления
От: Achilleus Mantzios
Дата:
От: Andrew J. Kopciuch
Дата:
FAQ