Thread: Alternatives to SQL ...
Hi, I'm wondering about alternative accesses to a PostgreSQL data base by means other than SQL. I know one can map many things to SQL, but let me think outside the box for just a moment: - Sending a parse tree in XML for processing by the optimizer. This circumvents the SQL language and avoids the kinds of syntactic ideosyncrasies of SQL (e.g., where you put commas.) This is fairly trivial, but of course the question is, would it be worth it? - Sending an execution plan in XML directly to the executor. This now circumvents the SQL parser and optimizer. I know this in in a way against the relational doxology and I don't take that light-heartedly. However, isn't it true that most optimizers cannot deal very well with more than 6 joins? I may be wrong, but I find myself spending quite a bit of time fighting with the Oracle or PostgreSQL optimizer to convince it to choose the plan that I want. There is so much magic to it with hints and the way you write SQL (where in relational theory the expressions are equivalent, they make huge difference in what plan is being generated.) So, it appears to me almost easier to just send a plan directly and have the system execute that plan. - These direct interfaces could be a nice way to experiment with new strategies without having to implement it on all three layers (SQL language, optimizer, and executor.) You noticed I sneaked in XML as the interface, and that would be neat because with XSLT it's so easy to manipulate. But I'm also thinking about a Prolog binding or constraint logic programming binding, that might be better optimizeable if it goes through a more direct path than SQL. Am I crazy? -Gunther -- Gunther Schadow, M.D., Ph.D. gschadow@regenstrief.org Medical Information Scientist Regenstrief Institute for Health Care Adjunct Assistant Professor Indiana University School of Medicine tel:1(317)630-7960 http://aurora.regenstrief.org
The SQL interface is bullet-proof because it validates tables, computes offsets, and stuff like that. Passing something else into the database and bypassing the SQL stage would require rewriting all the C logic for SQL to match your new language --- a lot of work for little gain. --------------------------------------------------------------------------- Gunther Schadow wrote: > Hi, > > I'm wondering about alternative accesses to a PostgreSQL data base > by means other than SQL. I know one can map many things to SQL, but > let me think outside the box for just a moment: > > - Sending a parse tree in XML for processing by the optimizer. > This circumvents the SQL language and avoids the kinds of > syntactic ideosyncrasies of SQL (e.g., where you put commas.) > This is fairly trivial, but of course the question is, would > it be worth it? > > - Sending an execution plan in XML directly to the executor. > This now circumvents the SQL parser and optimizer. I know this > in in a way against the relational doxology and I don't take that > light-heartedly. However, isn't it true that most optimizers > cannot deal very well with more than 6 joins? I may be wrong, > but I find myself spending quite a bit of time fighting with the > Oracle or PostgreSQL optimizer to convince it to choose the plan > that I want. There is so much magic to it with hints and the > way you write SQL (where in relational theory the expressions are > equivalent, they make huge difference in what plan is being > generated.) So, it appears to me almost easier to just send a > plan directly and have the system execute that plan. > > - These direct interfaces could be a nice way to experiment with > new strategies without having to implement it on all three > layers (SQL language, optimizer, and executor.) > > You noticed I sneaked in XML as the interface, and that would be > neat because with XSLT it's so easy to manipulate. But I'm also > thinking about a Prolog binding or constraint logic programming > binding, that might be better optimizeable if it goes through a > more direct path than SQL. > > Am I crazy? > -Gunther > > -- > Gunther Schadow, M.D., Ph.D. gschadow@regenstrief.org > Medical Information Scientist Regenstrief Institute for Health Care > Adjunct Assistant Professor Indiana University School of Medicine > tel:1(317)630-7960 http://aurora.regenstrief.org > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Fri, May 24, 2002 at 12:43:36PM -0500, Gunther Schadow wrote: > - Sending a parse tree in XML for processing by the optimizer. > This circumvents the SQL language and avoids the kinds of > syntactic ideosyncrasies of SQL (e.g., where you put commas.) > This is fairly trivial, but of course the question is, would > it be worth it? I don't know if you can design something in XML that is expressive and simple enough to compete with SQL. SQL is a simple language, why replace it with something unless it is demonstrably better. > - Sending an execution plan in XML directly to the executor. > This now circumvents the SQL parser and optimizer. I know this > in in a way against the relational doxology and I don't take that > light-heartedly. However, isn't it true that most optimizers > cannot deal very well with more than 6 joins? I may be wrong, > but I find myself spending quite a bit of time fighting with the > Oracle or PostgreSQL optimizer to convince it to choose the plan > that I want. There is so much magic to it with hints and the > way you write SQL (where in relational theory the expressions are > equivalent, they make huge difference in what plan is being > generated.) So, it appears to me almost easier to just send a > plan directly and have the system execute that plan. The detail contained in plans is quite substantial (as you can see using EXPLAIN VERBOSE). I doubt you can rely on programmers getting all the details right. As for the join problem, some people get good results tweaking the genetic query optimiser using documented interfaces. And if you don't like the way the tables are joined, the INNER/OUTER/LEFT/RIGHT JOIN syntax in SQL allows you to force the order. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.