Обсуждение: switching default integer datatype to int8 and "IN (...)" clause
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/
> but a select like this takes ages (looooong time): > # select * from file where id = 1921773; > id | name > -----+---------------- > 1921777 | icons > I believe the reason is this : the numeric literal is first considered an int4 becuase it falls within the range of int4 (-2147483648 to +2147483647). try quoting the literal like this: # select * from file where id = '1921773'; This forces the literal to be evaluated. If you do an explain on that query ... you should see that the query planner uses the index as expected and that the condition used on the index is using the literal value cast to a big int. That's just my understanding anyway. Andy
Thanks Andy - this "solves" the problem(*1) on the first level, where I
know how to quote the params, so they must be evaluated / casted.
But when I get into the subselects, I don't know how to make postgres to
cast / evaluate the results of the subselect, so it again does only
sequential scan.
How do I make postgres cast ( or evaluate? ) the subselect ?, so that
when I do
# explain _the_right_select_quesry_with_subselect_
I want to get (Index Scan):
Index Scan using file_pkey on file (cost=0.00..5.01 rows=1 width=8) SubPlan -> Materialize
(cost=37209.28..37209.28rows=9535 width=8) -> Index Scan using parentid_name_idx on file (cost=0.00..37209.28
rows=9535width=8)
but now instead I'm getting with this:# explain select id from file where id in( select id from file where parentid
='355764');
I don't want to get (Seq Scan): (that's what I'm getting now with the above query)
Seq Scan on file (cost=0.00..70956514802.83 rows=953478 width=8) SubPlan -> Materialize (cost=37209.28..37209.28
rows=9535width=8) -> Index Scan using parentid_name_idx on file (cost=0.00..37209.28 rows=9535 width=8)
What's the right _the_right_select_quesry_with_subselect_ with possibly
several nested subselects ?
Thanks,
John
(*1) PS: I guess the problem is that somehow postgres doesn't know by
default that it should try to "cast" the results of the subselects into
type that it is to be comparing it with. (which is int8). Is there a way
to formulate the query to ask for the cast, perhaps explicitly ? Or is
there a way to set a variable or some other condition which will tell
postgres to perform this cast implicitly ? -- Thanx !
On Thu, 9 Jan 2003, Andrew J. Kopciuch wrote:
> > but a select like this takes ages (looooong time):
> > # select * from file where id = 1921773;
> > id | name
> > -----+----------------
> > 1921777 | icons
> >
>
> I believe the reason is this : the numeric literal is first considered an int4
> becuase it falls within the range of int4 (-2147483648 to +2147483647).
>
> try quoting the literal like this:
>
> # select * from file where id = '1921773';
>
> This forces the literal to be evaluated. If you do an explain on that query
> ... you should see that the query planner uses the index as expected and that
> the condition used on the index is using the literal value cast to a big int.
>
>
> That's just my understanding anyway.
>
>
> Andy
--
-- Gospel of Jesus' kingdom = saving power of God for all who believe -- ## To some, nothing is
impossible.## http://Honza.Vicherek.com/