Обсуждение: EXISTS optimization

I'm posting this to performance in case our workaround may be of benefit to someone with a similar issue.  I'm posting
tohackers because I hope we can improve our planner in this area so that a workaround is not necessary.  (It might make
senseto reply to one group or the other, depending on reply content.) 

We are converting from a commercial database (which shall remain unnamed here, due to license restrictions on
publishingbenchmarks).  Most queries run faster on PostgreSQL; a small number choose very poor plans and run much
longer. This particular query runs on the commercial product in 6.1s first time, 1.4s cached.  In PostgreSQL it runs in
about144s both first time and cached.  I was able to use an easy but fairly ugly rewrite (getting duplicate rows and
eliminatingthem with DISTINCT) which runs on the commercial product in 9.2s/3.0s and in PostgreSQL in 2.0s/0.7s. 

Here are the tables:

          Table "public.TranHeader"
    Column     |       Type       | Modifiers
---------------+------------------+-----------
 tranNo        | "TranNoT"        | not null
 countyNo      | "CountyNoT"      | not null
 acctPd        | "DateT"          | not null
 date          | "DateT"          | not null
 isComplete    | boolean          | not null
 tranId        | "TranIdT"        | not null
 tranType      | "TranTypeT"      | not null
 userId        | "UserIdT"        | not null
 workstationId | "WorkstationIdT" | not null
 time          | "TimeT"          |
Indexes:
    "TranHeader_pkey" PRIMARY KEY, btree ("tranNo", "countyNo")
    "TranHeader_TranAcctPeriod" UNIQUE, btree ("acctPd", "tranNo", "countyNo")
    "TranHeader_TranDate" UNIQUE, btree (date, "tranNo", "countyNo")

            Table "public.TranDetail"
     Column      |        Type        | Modifiers
-----------------+--------------------+-----------
 tranNo          | "TranNoT"          | not null
 tranDetailSeqNo | "TranDetailSeqNoT" | not null
 countyNo        | "CountyNoT"        | not null
 acctCode        | "AcctCodeT"        | not null
 amt             | "MoneyT"           | not null
 assessNo        | "TranIdT"          |
 caseNo          | "CaseNoT"          |
 citnNo          | "CitnNoT"          |
 citnViolDate    | "DateT"            |
 issAgencyNo     | "IssAgencyNoT"     |
 partyNo         | "PartyNoT"         |
 payableNo       | "PayableNoT"       |
 rcvblNo         | "RcvblNoT"         |
Indexes:
    "TranDetail_pkey" PRIMARY KEY, btree ("tranNo", "tranDetailSeqNo", "countyNo")
    "TranDetail_TranDetCaseNo" UNIQUE, btree ("caseNo", "tranNo", "tranDetailSeqNo", "countyNo")
    "TranDetail_TranDetPay" UNIQUE, btree ("payableNo", "tranNo", "tranDetailSeqNo", "countyNo")
    "TranDetail_TranDetRcvbl" UNIQUE, btree ("rcvblNo", "tranNo", "tranDetailSeqNo", "countyNo")
    "TranDetail_TranDetAcct" btree ("acctCode", "citnNo", "countyNo")

              Table "public.Adjustment"
     Column      |         Type          | Modifiers
-----------------+-----------------------+-----------
 adjustmentNo    | "TranIdT"             | not null
 countyNo        | "CountyNoT"           | not null
 date            | "DateT"               | not null
 isTranVoided    | boolean               | not null
 reasonCode      | "ReasonCodeT"         | not null
 tranNo          | "TranNoT"             | not null
 adjustsTranId   | "TranIdT"             |
 adjustsTranNo   | "TranNoT"             |
 adjustsTranType | "TranTypeT"           |
 explanation     | character varying(50) |
Indexes:
    "Adjustment_pkey" PRIMARY KEY, btree ("adjustmentNo", "countyNo")
    "Adjustment_AdjustsTranId" btree ("adjustsTranId", "adjustsTranType", "tranNo", "countyNo")
    "Adjustment_AdjustsTranNo" btree ("adjustsTranNo", "tranNo", "countyNo")
    "Adjustment_Date" btree (date, "countyNo")

Admittedly, the indexes are optimized for our query load under the commercial product, which can use the "covering
index"optimization. 

explain analyze
SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date", "H"."userId", "H"."time"
  FROM "Adjustment" "A"
  JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND "H"."countyNo" = "A"."countyNo" AND "H"."tranNo" =
"A"."tranNo")
  WHERE "H"."tranType" = 'A'
    AND "A"."date" > DATE '2006-01-01'
    AND "H"."countyNo" = 66
    AND "A"."countyNo" = 66
    AND EXISTS
        (
          SELECT 1 FROM "TranDetail" "D"
            WHERE "D"."tranNo" = "H"."tranNo"
              AND "D"."countyNo" = "H"."countyNo"
              AND "D"."caseNo" LIKE '2006TR%'
        )
;

 Nested Loop  (cost=182.56..72736.37 rows=1 width=46) (actual time=6398.108..143631.427 rows=2205 loops=1)
   Join Filter: (("H"."tranId")::bpchar = ("A"."adjustmentNo")::bpchar)
   ->  Bitmap Heap Scan on "Adjustment" "A"  (cost=182.56..1535.69 rows=11542 width=22) (actual time=38.098..68.324
rows=12958loops=1) 
         Recheck Cond: (((date)::date > '2006-01-01'::date) AND (("countyNo")::smallint = 66))
         ->  Bitmap Index Scan on "Adjustment_Date"  (cost=0.00..179.67 rows=11542 width=0) (actual time=32.958..32.958
rows=12958loops=1) 
               Index Cond: (((date)::date > '2006-01-01'::date) AND (("countyNo")::smallint = 66))
   ->  Index Scan using "TranHeader_pkey" on "TranHeader" "H"  (cost=0.00..6.15 rows=1 width=46) (actual
time=11.073..11.074rows=0 loops=12958) 
         Index Cond: ((("H"."tranNo")::integer = ("A"."tranNo")::integer) AND (("H"."countyNo")::smallint = 66))
         Filter: ((("tranType")::bpchar = 'A'::bpchar) AND (subplan))
         SubPlan
           ->  Index Scan using "TranDetail_TranDetCaseNo" on "TranDetail" "D"  (cost=0.00..4.73 rows=1 width=0)
(actualtime=11.038..11.038 rows=0 loops=12958) 
                 Index Cond: ((("caseNo")::bpchar >= '2006TR'::bpchar) AND (("caseNo")::bpchar < '2006TS'::bpchar) AND
(("tranNo")::integer= ($0)::integer) AND (("countyNo")::smallint = ($1)::smallint)) 
                 Filter: (("caseNo")::bpchar ~~ '2006TR%'::text)
 Total runtime: 143633.838 ms

The commercial product scans the index on caseNo in TranDetail to build a work table of unique values, then uses
indexedaccess to the TranHeader and then to Adjustment.  I was able to get approximately the same plan (except the
duplicatesare eliminated at the end) in PostgreSQL by rewriting to this: 

SELECT DISTINCT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date", "H"."userId", "H"."time"
  FROM "Adjustment" "A"
  JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND "H"."countyNo" = "A"."countyNo" AND "H"."tranNo" =
"A"."tranNo")
  JOIN "TranDetail" "D" ON ("D"."tranNo" = "H"."tranNo" AND "D"."countyNo" = "H"."countyNo" AND "D"."caseNo" LIKE
'2006TR%')
  WHERE "H"."tranType" = 'A'
    AND "A"."date" > DATE '2006-01-01'
    AND "H"."countyNo" = 66
    AND "A"."countyNo" = 66
;

 Unique  (cost=130.96..130.98 rows=1 width=46) (actual time=694.591..715.008 rows=2205 loops=1)
   ->  Sort  (cost=130.96..130.96 rows=1 width=46) (actual time=694.586..701.808 rows=16989 loops=1)
         Sort Key: "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H".date, "H"."userId", "H"."time"
         ->  Nested Loop  (cost=0.00..130.95 rows=1 width=46) (actual time=0.157..636.779 rows=16989 loops=1)
               Join Filter: (("H"."tranNo")::integer = ("A"."tranNo")::integer)
               ->  Nested Loop  (cost=0.00..113.76 rows=4 width=50) (actual time=0.131..452.544 rows=16989 loops=1)
                     ->  Index Scan using "TranDetail_TranDetCaseNo" on "TranDetail" "D"  (cost=0.00..27.57 rows=20
width=6)(actual time=0.049..83.005 rows=46293 loops=1) 
                           Index Cond: ((("caseNo")::bpchar >= '2006TR'::bpchar) AND (("caseNo")::bpchar <
'2006TS'::bpchar)AND (66 = ("countyNo")::smallint)) 
                           Filter: (("caseNo")::bpchar ~~ '2006TR%'::text)
                     ->  Index Scan using "TranHeader_pkey" on "TranHeader" "H"  (cost=0.00..4.30 rows=1 width=46)
(actualtime=0.006..0.007 rows=0 loops=46293) 
                           Index Cond: ((("D"."tranNo")::integer = ("H"."tranNo")::integer) AND
(("H"."countyNo")::smallint= 66)) 
                           Filter: (("tranType")::bpchar = 'A'::bpchar)
               ->  Index Scan using "Adjustment_pkey" on "Adjustment" "A"  (cost=0.00..4.28 rows=1 width=22) (actual
time=0.007..0.008rows=1 loops=16989) 
                     Index Cond: ((("H"."tranId")::bpchar = ("A"."adjustmentNo")::bpchar) AND
(("A"."countyNo")::smallint= 66)) 
                     Filter: ((date)::date > '2006-01-01'::date)
 Total runtime: 715.932 ms

I can't see any reason that PostgreSQL can't catch up to the other product on this optimization issue.  This usage of
DISTINCTseems a bit sloppy; I usually try to dissuade the application programmers from accumulating duplicates during
thejoins and then eliminating them in this way. 

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> explain analyze
> SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date", "H"."userId", "H"."time"
>   FROM "Adjustment" "A"
>   JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND "H"."countyNo" = "A"."countyNo" AND "H"."tranNo" =
"A"."tranNo")
>   WHERE "H"."tranType" = 'A'
>     AND "A"."date" > DATE '2006-01-01'
>     AND "H"."countyNo" = 66
>     AND "A"."countyNo" = 66
>     AND EXISTS
>         (
>           SELECT 1 FROM "TranDetail" "D"
>             WHERE "D"."tranNo" = "H"."tranNo"
>               AND "D"."countyNo" = "H"."countyNo"
>               AND "D"."caseNo" LIKE '2006TR%'
>         )
> ;

> The commercial product scans the index on caseNo in TranDetail to build a work table of unique values, then uses
indexedaccess to the TranHeader and then to Adjustment. 

If you want that, try rewriting the EXISTS to an IN:

   AND ("H"."tranNo", "H"."countyNo") IN
        (
          SELECT "D"."tranNo", "D"."countyNo" FROM "TranDetail" "D"
            WHERE "D"."caseNo" LIKE '2006TR%'
        )

We don't currently try to flatten EXISTS into a unique/join plan as we
do for IN.  I seem to recall not doing so when I rewrote IN planning
because I didn't think it would be exactly semantically equivalent,
but that was awhile ago.  Right at the moment it seems like it ought
to be equivalent as long as the comparison operators are strict.

            regards, tom lane

>>> On Fri, Mar 23, 2007 at  4:49 PM, in message <25339.1174686582@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> explain analyze
>> SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date",
> "H"."userId", "H"."time"
>>   FROM "Adjustment" "A"
>>   JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND
> "H"."countyNo" = "A"."countyNo" AND "H"."tranNo" = "A"."tranNo")
>>   WHERE "H"."tranType" = 'A'
>>     AND "A"."date" > DATE '2006- 01- 01'
>>     AND "H"."countyNo" = 66
>>     AND "A"."countyNo" = 66
>>     AND EXISTS
>>         (
>>           SELECT 1 FROM "TranDetail" "D"
>>             WHERE "D"."tranNo" = "H"."tranNo"
>>               AND "D"."countyNo" = "H"."countyNo"
>>               AND "D"."caseNo" LIKE '2006TR%'
>>         )
>> ;
>
>> The commercial product scans the index on caseNo in TranDetail to build a
> work table of unique values, then uses indexed access to the TranHeader and
> then to Adjustment.
>
> If you want that, try rewriting the EXISTS to an IN:
>
>    AND ("H"."tranNo", "H"."countyNo") IN
>         (
>           SELECT "D"."tranNo", "D"."countyNo" FROM "TranDetail" "D"
>             WHERE "D"."caseNo" LIKE '2006TR%'
>         )

Nice.  I get this:

explain analyze
SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date", "H"."userId", "H"."time"
  FROM "Adjustment" "A"
  JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND "H"."countyNo" = "A"."countyNo" AND "H"."tranNo" =
"A"."tranNo")
  WHERE "H"."tranType" = 'A'
    AND "A"."date" > DATE '2006- 01- 01'
    AND "H"."countyNo" = 66
    AND "A"."countyNo" = 66
    AND ("H"."tranNo", "H"."countyNo") IN
        (
          SELECT "D"."tranNo", "D"."countyNo" FROM "TranDetail" "D"
            WHERE "D"."caseNo" LIKE '2006TR%'
        )
;

 Nested Loop  (cost=27.76..36.38 rows=1 width=46) (actual time=92.999..200.398 rows=2209 loops=1)
   Join Filter: (("H"."tranNo")::integer = ("A"."tranNo")::integer)
   ->  Nested Loop  (cost=27.76..32.08 rows=1 width=50) (actual time=92.970..176.472 rows=2209 loops=1)
         ->  HashAggregate  (cost=27.76..27.77 rows=1 width=6) (actual time=92.765..100.810 rows=9788 loops=1)
               ->  Index Scan using "TranDetail_TranDetCaseNo" on "TranDetail" "D"  (cost=0.00..27.66 rows=20 width=6)
(actualtime=0.059..60.967 rows=46301 loops=1) 
                     Index Cond: ((("caseNo")::bpchar >= '2006TR'::bpchar) AND (("caseNo")::bpchar < '2006TS'::bpchar)
AND(("countyNo")::smallint = 66)) 
                     Filter: (("caseNo")::bpchar ~~ '2006TR%'::text)
         ->  Index Scan using "TranHeader_pkey" on "TranHeader" "H"  (cost=0.00..4.30 rows=1 width=46) (actual
time=0.006..0.006rows=0 loops=9788) 
               Index Cond: ((("H"."tranNo")::integer = ("D"."tranNo")::integer) AND (("H"."countyNo")::smallint = 66))
               Filter: (("tranType")::bpchar = 'A'::bpchar)
   ->  Index Scan using "Adjustment_pkey" on "Adjustment" "A"  (cost=0.00..4.28 rows=1 width=22) (actual
time=0.008..0.009rows=1 loops=2209) 
         Index Cond: ((("H"."tranId")::bpchar = ("A"."adjustmentNo")::bpchar) AND (("A"."countyNo")::smallint = 66))
         Filter: ((date)::date > '2006-01-01'::date)
 Total runtime: 201.306 ms

That's the good news.  The bad news is that I operate under a management portability dictate which doesn't currently
allowthat syntax, since not all of the products they want to cover support it.  I tried something which seems
equivalent,but it is running for a very long time.  I'll show it with just the explain while I wait to see how long the
explainanalyze takes. 

explain
SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date", "H"."userId", "H"."time"
  FROM "Adjustment" "A"
  JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND "H"."countyNo" = "A"."countyNo" AND "H"."tranNo" =
"A"."tranNo")
  WHERE "H"."tranType" = 'A'
    AND "A"."date" > DATE '2006- 01- 01'
    AND "H"."countyNo" = 66
    AND "A"."countyNo" = 66
    AND "H"."tranNo" IN
        (
          SELECT "D"."tranNo" FROM "TranDetail" "D"
            WHERE "D"."caseNo" LIKE '2006TR%'
              AND "D"."countyNo" = "H"."countyNo"
        )
;

 Nested Loop  (cost=0.00..181673.08 rows=1 width=46)
   Join Filter: (("H"."tranId")::bpchar = ("A"."adjustmentNo")::bpchar)
   ->  Seq Scan on "Adjustment" "A"  (cost=0.00..2384.27 rows=11733 width=22)
         Filter: (((date)::date > '2006-01-01'::date) AND (("countyNo")::smallint = 66))
   ->  Index Scan using "TranHeader_pkey" on "TranHeader" "H"  (cost=0.00..15.27 rows=1 width=46)
         Index Cond: ((("H"."tranNo")::integer = ("A"."tranNo")::integer) AND (("H"."countyNo")::smallint = 66))
         Filter: ((("tranType")::bpchar = 'A'::bpchar) AND (subplan))
         SubPlan
           ->  Index Scan using "TranDetail_TranDetCaseNo" on "TranDetail" "D"  (cost=0.00..27.66 rows=20 width=4)
                 Index Cond: ((("caseNo")::bpchar >= '2006TR'::bpchar) AND (("caseNo")::bpchar < '2006TS'::bpchar) AND
(("countyNo")::smallint= ($0)::smallint)) 
                 Filter: (("caseNo")::bpchar ~~ '2006TR%'::text)

> We don't currently try to flatten EXISTS into a unique/join plan as we
> do for IN.  I seem to recall not doing so when I rewrote IN planning
> because I didn't think it would be exactly semantically equivalent,
> but that was awhile ago.  Right at the moment it seems like it ought
> to be equivalent as long as the comparison operators are strict.

There are a great many situations where they are exactly semantically equivalent.  In fact, the commercial database
productusually generates an identical plan.  I could try to work out (or better yet find) a formal description of when
thatequivalence holds, if someone would be up for implementing it.  Barring that, I could see if management would
approvesome time for me to look at submitting a patch, but I haven't looked at the code involved, so I have no idea of
thescale of effort involved yet. 

-Kevin

On Fri, Mar 23, 2007 at 05:49:42PM -0400, Tom Lane wrote:
> We don't currently try to flatten EXISTS into a unique/join plan as we
> do for IN.  I seem to recall not doing so when I rewrote IN planning
> because I didn't think it would be exactly semantically equivalent,
> but that was awhile ago.  Right at the moment it seems like it ought
> to be equivalent as long as the comparison operators are strict.

Wasn't it due to the fact that IN needs to scan through all
possibilites anyway because of its interaction with NULL, whereas
EXISTS can stop at the first row?

That would mean the subquery to be materialised would not be equivalent
if it called any non-immutable functions. It's also much less clear to
be a win in the EXISTs case. But then, that's a costs issue the planner
can deal with...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

I don't understand -- TRUE OR UNKNOWN evaluates to TRUE, so why would the IN need to continue?  I'm not quite following
therest; could you elaborate or give an example?  (Sorry if I'm lagging behind the rest of the class here.) 

-Kevin


>>> Martijn van Oosterhout <kleptog@svana.org> 03/23/07 5:26 PM >>>
On Fri, Mar 23, 2007 at 05:49:42PM -0400, Tom Lane wrote:
> We don't currently try to flatten EXISTS into a unique/join plan as we
> do for IN.  I seem to recall not doing so when I rewrote IN planning
> because I didn't think it would be exactly semantically equivalent,
> but that was awhile ago.  Right at the moment it seems like it ought
> to be equivalent as long as the comparison operators are strict.

Wasn't it due to the fact that IN needs to scan through all
possibilites anyway because of its interaction with NULL, whereas
EXISTS can stop at the first row?

That would mean the subquery to be materialised would not be equivalent
if it called any non-immutable functions. It's also much less clear to
be a win in the EXISTs case. But then, that's a costs issue the planner
can deal with...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

>>> On Fri, Mar 23, 2007 at  5:26 PM, in message
<46040DAC.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:

> I tried something which seems
> equivalent, but it is running for a very long time.  I'll show it with just
> the explain while I wait to see how long the explain analyze takes.
>
> explain
> SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date",
> "H"."userId", "H"."time"
>   FROM "Adjustment" "A"
>   JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND
> "H"."countyNo" = "A"."countyNo" AND "H"."tranNo" = "A"."tranNo")
>   WHERE "H"."tranType" = 'A'
>     AND "A"."date" > DATE '2006-  01-  01'
>     AND "H"."countyNo" = 66
>     AND "A"."countyNo" = 66
>     AND "H"."tranNo" IN
>         (
>           SELECT "D"."tranNo" FROM "TranDetail" "D"
>             WHERE "D"."caseNo" LIKE '2006TR%'
>               AND "D"."countyNo" = "H"."countyNo"
>         )
> ;

explain analyze results:

 Nested Loop  (cost=0.00..181673.08 rows=1 width=46) (actual time=42224.077..964266.969 rows=2209 loops=1)
   Join Filter: (("H"."tranId")::bpchar = ("A"."adjustmentNo")::bpchar)
   ->  Seq Scan on "Adjustment" "A"  (cost=0.00..2384.27 rows=11733 width=22) (actual time=15.355..146.620 rows=13003
loops=1)
         Filter: (((date)::date > '2006-01-01'::date) AND (("countyNo")::smallint = 66))
   ->  Index Scan using "TranHeader_pkey" on "TranHeader" "H"  (cost=0.00..15.27 rows=1 width=46) (actual
time=74.141..74.141rows=0 loops=13003) 
         Index Cond: ((("H"."tranNo")::integer = ("A"."tranNo")::integer) AND (("H"."countyNo")::smallint = 66))
         Filter: ((("tranType")::bpchar = 'A'::bpchar) AND (subplan))
         SubPlan
           ->  Index Scan using "TranDetail_TranDetCaseNo" on "TranDetail" "D"  (cost=0.00..27.66 rows=20 width=4)
(actualtime=0.039..58.234 rows=42342 loops=13003) 
                 Index Cond: ((("caseNo")::bpchar >= '2006TR'::bpchar) AND (("caseNo")::bpchar < '2006TS'::bpchar) AND
(("countyNo")::smallint= ($0)::smallint)) 
                 Filter: (("caseNo")::bpchar ~~ '2006TR%'::text)
 Total runtime: 964269.555 ms

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If you want that, try rewriting the EXISTS to an IN:
>>
>> AND ("H"."tranNo", "H"."countyNo") IN
>> (
>> SELECT "D"."tranNo", "D"."countyNo" FROM "TranDetail" "D"
>> WHERE "D"."caseNo" LIKE '2006TR%'
>> )

> 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 cover support it.

Which part of it don't they like --- the multiple IN-comparisons?

> I tried something which seems equivalent, but it is running for a very
> long time.
>     AND "H"."tranNo" IN
>         (
>           SELECT "D"."tranNo" FROM "TranDetail" "D"
>             WHERE "D"."caseNo" LIKE '2006TR%'
>               AND "D"."countyNo" = "H"."countyNo"
>         )

No, that's not gonna accomplish a darn thing, because you've still got
a correlated subquery (ie, a reference to outer "H") and so turning the
IN into a join doesn't work.

            regards, tom lane

>>> On Fri, Mar 23, 2007 at  6:04 PM, in message
<b6e8f2e80703231604v72b9dc4dr51eebd62274d53ec@mail.gmail.com>, "Peter Kovacs"
<peter.kovacs.1.0rc@gmail.com> wrote:
> On 3/23/07, Kevin Grittner <Kevin.Grittner@wicourts.gov> 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
runs.

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
products.

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
reconsider.

-Kevin

On Fri, Mar 23, 2007 at 05:30:27PM -0500, Kevin Grittner wrote:
> I don't understand -- TRUE OR UNKNOWN evaluates to TRUE, so why would
> the IN need to continue?  I'm not quite following the rest; could you
> elaborate or give an example?  (Sorry if I'm lagging behind the rest
> of the class here.)

You're right, I'm getting confused with the interaction of NULL and NOT
IN.

The multiple evaluation thing still applies, but that's minor.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On 3/23/07, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
[...]
> That's the good news.  The bad news is that I operate under a management portability dictate which doesn't currently
allowthat 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?

Thanks
Peter

cover support it.  I tried something which seems equivalent, but it is
running for a very long time.  I'll show it with just the explain
while I wait to see how long the explain analyze takes.
>
[...]