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 обсуждение исходный текст |
Ответы |
Re: switching default integer datatype to int8 and "IN (...)" clause
|
Список | pgsql-sql |
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 Scanusing 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 Scanusing 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) -> SeqScan 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 по дате отправления: