"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%'
)

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

>>> 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) (actual time=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.006 rows=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.009 rows=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 allow that 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 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"
)
;

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 product usually generates an identical plan. I could try to work out (or better yet find) a formal description of when that equivalence holds, if someone would be up for implementing it. Barring that, I could see if management would approve some time for me to look at submitting a patch, but I haven't looked at the code involved, so I have no idea of the scale of effort involved yet.

-Kevin



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFGBFQhIB7bNG8LQkwRAmPjAJ497gV2RMuAR8/teCufxkXg0ndYvACggMPQ
4R2NOurkfXngXW4lJS38icw=
=91bh
-----END PGP SIGNATURE-----

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

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



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

>>> 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.141 rows=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) (actual time=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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

>>> 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 help with 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 than when 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 to run it. The ANSI source is broken down to "lowest common denominator" queries, with all procedural code covered in the Java 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" routines which capture before and after images and save them in our own transaction image tables. We use this to replicate from our 72 county databases, which are the official court record, to multiple central databases, and a transaction repository, 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 idea what to do when you then want to drop in some different product. We have a plugin layer to manage known areas of differences which 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 has brought 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 candidate database products, either directly or through a plugin layer. If any of the products don't support multi-value row 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 the syntax 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 portability rules. No sale. Perhaps when everything is converted to PostgreSQL and working for a while they may reconsider.

-Kevin



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFGBSKvIB7bNG8LQkwRApA6AJ40lL5YJ4WWLALouegPzA cvR0qRWACfTjyk
FxcQQTAy5I8SRMcAwJXYn3U=
=B7c3
-----END PGP SIGNATURE-----

Kevin Grittner wrote:
> Management has simply given a mandate that the software be independent
> of OS and database vendor, and to use Java to help with the OS independence.
> ... we write all of our queries in ANSI SQL in our own query tool, parse it,
> and generate Java classes to run it.

A better solution, and one I've used for years, is to use OS- or database-specific features, but carefully encapsulate them in a single module, for example, "database_specific.java".

For example, when I started supporting both Oracle and Postgres, I encountered the MAX() problem, which (at the time) was very slow in Postgres, but could be replaced by "select X from MYTABLE order by X desc limit 1". So I created a function, "GetColumnMax()" that encapsulates the database-specific code for this. Similar functions encapsulate and a number of other database-specific optimizations.

Another excellent example: I have a function called "TableExists(name)". To the best of my knowledge, there simply is no ANSI SQL for this, so what do you do? Encapsulate it in one place.

The result? When I port to a new system, I know exactly where to find all of the non-ANSI SQL. I started this habit years ago with C/C++ code, which has the same problem: System calls are not consistent across the varients of Unix, Windows, and other OS's. So you put them all in one file called "machine_dependent.c".

Remember the old adage: There is no such thing as portable code, only code that has been ported.

Cheers,
Craig



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org