Re: [HACKERS] EXISTS optimization

From: Kevin Grittner
Subject: Re: [HACKERS] EXISTS optimization
Date: ,
(view: Whole thread, Raw)
In response to: EXISTS optimization  ("Kevin Grittner")
Responses: Re: [HACKERS] EXISTS optimization  ("Craig A. James")
List: pgsql-performance

Tree view

EXISTS optimization  ("Kevin Grittner", )
 Re: [HACKERS] EXISTS optimization  (Tom Lane, )
  Re: [HACKERS] EXISTS optimization  ("Kevin Grittner", )
   Re: [HACKERS] EXISTS optimization  ("Kevin Grittner", )
   Re: [HACKERS] EXISTS optimization  (Tom Lane, )
  Re: [HACKERS] EXISTS optimization  (Martijn van Oosterhout, )
   Re: [HACKERS] EXISTS optimization  ("Kevin Grittner", )
    Re: [HACKERS] EXISTS optimization  (Martijn van Oosterhout, )
 Re: [HACKERS] EXISTS optimization  ("Kevin Grittner", )
  Re: [HACKERS] EXISTS optimization  ("Craig A. James", )

>>> On Fri, Mar 23, 2007 at  6:04 PM, in message
<>, "Peter Kovacs"
<> wrote:
> On 3/23/07, Kevin Grittner <> wrote:
> [...]
>> That's the good news.  The bad news is that I operate under a management
> portability dictate which doesn't currently allow that syntax, since not all
> of the products they want to
> It doesn't really touch the substance, but I am curious: are you not
> even allowed to discriminate between products in your code like:
> if db is 'postresql' then
> ...
> else
> ...
> ?
> What would be the rationale for that?

Anybody who's not curious about that should skip the rest of this email.

Management has simply given a mandate that the software be independent of OS and database vendor, and to use Java to
helpwith the OS independence.  I have to admit that I am the architect of the database independence solution that was
devised. (The choice of Java for the OS independence has been very successful.  We have run our bytecode on HP-UX,
Windows,Sun Solaris, and various flavors of Linux without having to compile different versions of the bytecode.  Other
thanwhen people get careless with case sensitivity on file names or with path separators, it just drops right in and

For the data side, we write all of our queries in ANSI SQL in our own query tool, parse it, and generate Java classes
torun it.  The ANSI source is broken down to "lowest common denominator" queries, with all procedural code covered in
theJava query classes.  So we have stored procedures which can be called, triggers that fire, etc. in Java, issuing
SELECT,INSERT, UPDATE, DELETE statements to the database.  This allows us to funnel all DML through a few "primitive"
routineswhich capture before and after images and save them in our own transaction image tables.  We use this to
replicatefrom our 72 county databases, which are the official court record, to multiple central databases, and a
transactionrepository, used for auditing case activity and assisting with failure recovery. 

The problem with burying 'if db is MaxDB', 'if db is SQLServer', 'if db is PostgreSQL' everywhere is that you have no
ideawhat to do when you then want to drop in some different product.   We have a plugin layer to manage known areas of
differenceswhich aren't handled cleanly by JDBC, where the default behavior is ANSI-compliant, and a few dozen to a few
hundred lines need to be written to modify that default support a new database product.  (Of course, each one so far
hasbrought in a few surprises, making the plugin layer just a little bit thicker.) 

So, to support some new syntax, we have to update our parser, and have a way to generate code which runs on all the
candidatedatabase products, either directly or through a plugin layer.  If any of the products don't support
multi-valuerow value constructors, I have a hard time seeing a good way to cover that with the plugin.  On the subject
issue,I'm pretty sure it would actually be less work for me to modify the PostgreSQL optimizer to efficiently handle
thesyntax we do support than to try to bend row value constructors to a syntax that is supported on other database

And, by the way, I did take a shot on getting them to commit to PostgreSQL as the long-term solution, and relax the
portabilityrules.  No sale.  Perhaps when everything is converted to PostgreSQL and working for a while they may


pgsql-performance by date:

From: "amrit angsusingh"
Subject: Optimization postgresql 8.1.4 FC 6 X64 ?
From: Heikki Linnakangas
Subject: Re: Optimization postgresql 8.1.4 FC 6 X64 ?