pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Implicit casting and JOIN syntax constraints 2+ messages / 2 participants [nested] [flat]
* Implicit casting and JOIN syntax constraints @ 2003-01-17 14:48 Roman Fail <rfail@posportal.com> 2003-01-17 15:41 ` Re: Implicit casting and JOIN syntax constraints Andrew Sullivan <andrew@libertyrms.info> 0 siblings, 1 reply; 2+ messages in thread From: Roman Fail @ 2003-01-17 14:48 UTC (permalink / raw) To: Jeff <threshar@torgo.978.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; sszabo@megazone23.bigpanda.com; pgsql-performance >> HOWEVER.....look at this: >> EXPLAIN ANALYZE select batchdetailid from batchdetail where batchdetailid = 27321::bigint; >> Index Scan using batchdetail_pkey on batchdetail (cost=0.00..4.13 rows=1 width=8) (actual time=0.03..0.03 rows=1 loops=1) >> Index Cond: (batchdetailid = 27321::bigint) >> Total runtime: 0.07 msec > Jeff Trout wrote: > We had this happen to us - we had a serial8 column (int8) and our query > was straight forward where id = 12345; which ran craptacularly. After > much head banging and cursing I had tried where id = '12345' and it > magically worked. I think the parser is interpreting a "number" to be an > int4 instead of int8. (instead of quotes you can also cast via > 12345::int8 like you did) > Perhaps this should go on the TODO - when one side is an int8 and the > other is a literal number assume the number to be int8 instead of int4? It seems to me that this should absolutely go on the TODO list. Why does the planner require an explicit cast when the implicit cast is so obvious? Does Oracle do this? I can assure you that MSSQL does not. If getting more people to migrate to PostgreSQL is a major goal these days, it's got to be relatively easy. I think that almost everyone coming from a MSSQL or Access background is going to have big problems with this. And the other issue of the JOIN syntax constraining the planner - you've got to be able to turn that off too. I've been writing SQL queries for 10 years in FoxPro, Access, SQL Server, MySQL, and Sybase. I have never come across this very confusing "feature" until now. How do we go about voting an issue onto the TODO list? These two get my vote for sure! Roman ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Implicit casting and JOIN syntax constraints 2003-01-17 14:48 Implicit casting and JOIN syntax constraints Roman Fail <rfail@posportal.com> @ 2003-01-17 15:41 ` Andrew Sullivan <andrew@libertyrms.info> 0 siblings, 0 replies; 2+ messages in thread From: Andrew Sullivan @ 2003-01-17 15:41 UTC (permalink / raw) To: pgsql-performance On Fri, Jan 17, 2003 at 06:48:28AM -0800, Roman Fail wrote: > It seems to me that this should absolutely go on the TODO list. > Why does the planner require an explicit cast when the implicit > cast is so obvious? Does Oracle do this? I can assure you that > MSSQL does not. The reason it happens is because of the flexible datatype system in PostgreSQL. Because it's easy to add a datatype, you pay in other ways. The problem is coming up with a nice, clean set of rules for coercion. See the link that Tom Lane posted, and the thousands of other discussions around this in the archives. Yes, it's a pain. Everyone knows that. A complete solution is what's missing. > too. I've been writing SQL queries for 10 years in FoxPro, Access, > SQL Server, MySQL, and Sybase. I have never come across this very > confusing "feature" until now. Well, there are differences between every system. Indeed, the "SQL" of MySQL is so far from anything resembling the standard that one could argue it doesn't comply at all. You're right that it means a steep learning curve for some things, and the problems can be frustrating. But that doesn't mean you want to throw the baby out with the bathwater. The ability to give the planner hints through the JOIN syntax is, frankly, a real help when you're faced with certain kinds of performance problems. Some systems don't give you a knob to tune there at all. Is it different from other systems? Sure. Is that automatically a reason to pitch the feature? No. (Further discussion of this probably belongs on -general, if anywhere, by the way.) A ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-01-17 15:41 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-01-17 14:48 Implicit casting and JOIN syntax constraints Roman Fail <rfail@posportal.com> 2003-01-17 15:41 ` Andrew Sullivan <andrew@libertyrms.info>
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