pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedswitching default integer datatype to int8 and "IN (...)" clause
3+ messages / 2 participants
[nested] [flat]
* switching default integer datatype to int8 and "IN (...)" clause
@ 2003-01-09 09:24 postgres@ied.com
0 siblings, 1 reply; 3+ messages in thread
From: postgres@ied.com @ 2003-01-09 09:24 UTC (permalink / raw)
To: 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 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/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: switching default integer datatype to int8 and "IN (...)" clause
@ 2003-01-09 10:30 Andrew J. Kopciuch <akopciuch@bddf.ca>
parent: postgres@ied.com
0 siblings, 1 reply; 3+ messages in thread
From: Andrew J. Kopciuch @ 2003-01-09 10:30 UTC (permalink / raw)
To: postgres@ied.com; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: switching default integer datatype to int8 and "IN (...)"
@ 2003-01-12 00:03 postgres@ied.com
parent: Andrew J. Kopciuch <akopciuch@bddf.ca>
0 siblings, 0 replies; 3+ messages in thread
From: postgres@ied.com @ 2003-01-12 00:03 UTC (permalink / raw)
To: Andrew J. Kopciuch <akopciuch@bddf.ca>; +Cc: pgsql-sql
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.28 rows=9535 width=8)
-> Index Scan using parentid_name_idx on file (cost=0.00..37209.28 rows=9535 width=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=9535 width=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/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-01-12 00:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-09 09:24 switching default integer datatype to int8 and "IN (...)" clause postgres@ied.com
2003-01-09 10:30 ` Andrew J. Kopciuch <akopciuch@bddf.ca>
2003-01-12 00:03 ` Re: switching default integer datatype to int8 and "IN (...)" postgres@ied.com
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