pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex on different types
2+ messages / 2 participants
[nested] [flat]
* index on different types
@ 2005-04-29 02:35 Enrico Weigelt <weigelt@metux.de>
2005-04-29 03:28 ` Re: index on different types Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 2+ messages in thread
From: Enrico Weigelt @ 2005-04-29 02:35 UTC (permalink / raw)
To: pgsql-performance
Hi folks,
there's often some talk about indices cannot be used if datatypes
dont match.
On a larger (and long time growed) application I tend to use OID
for references on new tables while old stuff is using integer.
Is the planner smart enough to see both as compatible datatype
or is manual casting required ?
thx
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.de
---------------------------------------------------------------------
Realtime Forex/Stock Exchange trading powered by postgresSQL :))
http://www.fxignal.net/
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: index on different types
2005-04-29 02:35 index on different types Enrico Weigelt <weigelt@metux.de>
@ 2005-04-29 03:28 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2005-04-29 03:28 UTC (permalink / raw)
To: pgsql-performance
On Fri, Apr 29, 2005 at 04:35:13AM +0200, Enrico Weigelt wrote:
>
> there's often some talk about indices cannot be used if datatypes
> dont match.
PostgreSQL 8.0 is smarter than previous versions in this respect.
It'll use an index if possible even when the types don't match.
> On a larger (and long time growed) application I tend to use OID
> for references on new tables while old stuff is using integer.
If you're using OIDs as primary keys then you might wish to reconsider.
See the caveats in the documentation and in the FAQ:
http://www.postgresql.org/docs/8.0/interactive/datatype-oid.html
http://www.postgresql.org/docs/faqs.FAQ.html#4.12
> Is the planner smart enough to see both as compatible datatype
> or is manual casting required ?
You can use EXPLAIN to see what the planner will do, but be aware
that the planner won't always use an index even if it could: if it
thinks a sequential scan would be faster then it won't use an index.
To see if using an index is possible, you could set enable_seqscan
to off before executing EXPLAIN. In any case, a foreign key column
probably ought to have the same type as the column it references --
is there a reason for making them different?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-04-29 03:28 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-29 02:35 index on different types Enrico Weigelt <weigelt@metux.de>
2005-04-29 03:28 ` Michael Fuhr <mike@fuhr.org>
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