Tom Lane <[EMAIL PROTECTED]> writes:
> Jack Coates <[EMAIL PROTECTED]> writes:
> > yup -- here it is. It will probably be a nasty mess after linewrap gets
> > done with it,
>
> yup, sure is :-( If I was familiar with the layout I could probably
> decipher where the line breaks are supposed to be, but right now I'm
> just confused.
I just replaced all newlines that are followed by lines starting in column 1
with spaces and got something reasonable:
SELECT DISTINCT members_.memberid_ FROM members_ WHERE ( members_.List_='list1'
AND members_.MemberType_='normal' AND members_.SubType_='mail' ) GROUP BY
memberid_ HAVING ( ( select count(*) from lyrActiveRecips, outmail_ where
outmail 11 1 0 NULL NULL 1 NULL 102274.5
NULL NULL NULL 104.10356 NULL NULL SELECT 0 NULL
|--Parallelism(Gather Streams) 11 2 1 Parallelism Gather
Streams NULL NULL 102274.5 0.0 0.22011127 23 104.10356
[members_].[MemberID_] NULL PLAN_ROW -1 1.0
|--Filter(WHERE:(If ([Expr1006] IS NULL) then 0 else [Expr1006]+If ([Expr1012]
IS NULL) then 0 else [Expr1012]=3)) 11 3 2 Filter Filter
WHERE:(If ([Expr1006] IS NULL) then 0 else [Expr1006]+If ([Expr1012] IS NULL) then 0
else [Expr1012]=3) NULL 102274.5 0.0 3.5393338 23 103.88345
[members_].[MemberID_] NULL PLAN_ROW -1 1.0
|--Hash Match(Right Outer Join,
HASH:([lyrCompletedRecips].[MemberID])=([members_].[MemberID_]),
RESIDUAL:([members_].[MemberID_]=[lyrCompletedRecips].[MemberID])) 11 4 3
Hash Match Right Outer Join
HASH:([lyrCompletedRecips].[MemberID])=([members_].[MemberID_]),
RESIDUAL:([members_].[MemberID_]=[lyrCompletedRecips].[MemberID]) NULL
4782883.5 0.0 21.874712 23 100.34412
[members_].[MemberID_], [Expr1006], [Expr1012] NULL PLAN_ROW -1 1.0
|--Compute Scalar(DEFINE:([Expr1012]=Convert([Expr1020]))) 11
5 4 Compute Scalar Compute Scalar
DEFINE:([Expr1012]=Convert([Expr1020])) [Expr1012]=Convert([Expr1020]) 119575.35
0.0 1.3723248 15 4.3749919 [lyrCompletedRecips].[MemberID],
[Expr1012] NULL PLAN_ROW -1 1.0
| |--Hash Match(Aggregate, HASH:([lyrCompletedRecips].[MemberID]),
RESIDUAL:([lyrCompletedRecips].[MemberID]=[lyrCompletedRecips].[MemberID])
DEFINE:([Expr1020]=COUNT(*))) 11 6 5 Hash Match Aggregate
HASH:([lyrCompletedRecips].[MemberID]),
RESIDUAL:([lyrCompletedRecips].[MemberID]=[lyrCompletedRecips].[MemberID])
[Expr1020]=COUNT(*) 119575.35 0.0 1.3723248 15 4.3749919
[lyrCompletedRecips].[MemberID], [Expr1020] NULL PLAN_ROW -1 1.0
| |--Parallelism(Repartition Streams, PARTITION
COLUMNS:([lyrCompletedRecips].[MemberID])) 11 7 6 Parallelism
Repartition Streams PARTITION COLUMNS:([lyrCompletedRecips].[MemberID]) NULL
119640.6 0.0 0.32407209 173 3.002667
[lyrCompletedRecips].[MemberID] NULL PLAN_ROW -1 1.0
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([outmail_].[MessageID_])) 11 8 7 Nested Loops Inner
Join OUTER REFERENCES:([outmail_].[MessageID_]) NULL 119640.6 0.0
0.75014657 173 2.6785948 [lyrCompletedRecips].[MemberID] NULL
PLAN_ROW -1 1.0
| |--Parallelism(Distribute Streams) 11 9
8 Parallelism Distribute Streams NULL NULL 1.0 0.0
2.8501874E-2 128 9.4664574E-2 [outmail_].[MessageID_] NULL PLAN_ROW
-1 1.0
| | |--Clustered Index
Scan(OBJECT:([lmdb].[dbo].[outmail_].[IX_outmail_list]),
WHERE:([outmail_].[Type_]='list')) 11 10 9 Clustered Index Scan
Clustered Index Scan OBJECT:([lmdb].[dbo].[outmail_].[IX_outmail_list]),
WHERE:([outmail_].[Type_]='list') [outmail_].[Type_], [outmail_].[MessageID_]
1.0 0.01878925 3.9800001E-5 128 3.7658099E-2 [outmail_].[Type_],
[outmail_].[MessageID_] NULL PLAN_ROW 0 1.0
| |--Clustered Index
Seek(OBJECT:([lmdb].[dbo].[lyrCompletedRecips].[IX_CompletedRecipsMailingID]),
SEEK:([lyrCompletedRecips].[MailingID]=[outmail_].[MessageID_]),
WHERE:([lyrCompletedRecips].[CompletionStatusID]=300 AN 11 11 8
Clustered Index Seek Clustered Index Seek
OBJECT:([lmdb].[dbo].[lyrCompletedRecips].[IX_CompletedRecipsMailingID]),
SEEK:([lyrCompletedRecips].[MailingID]=[outmail_].[MessageID_]),
WHERE:([lyrCompletedRecips].[CompletionStatusID]=300 AND
[lyrCompletedRecips].[FinalAttempt]>'Jan 20 2004 12:00AM')
[lyrCompletedRecips].[CompletionStatusID], [lyrCompletedRecips].[FinalAttempt],
[lyrCompletedRecips].[MemberID] 119640.6 0.5750553 0.13207871 53
1.5463468 [lyrCompletedRecips].[CompletionStatusID],
[lyrCompletedRecips].[FinalAttempt], [lyrCompletedRecips].[MemberID] NULL PLAN_ROW
-1 3.0
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([members_].[MemberID_])) 11 19 4 Parallelism
Repartition Streams PARTITION COLUMNS:([members_].[MemberID_]) NULL
4782883.5 0.0 15.474822 19 74.094414
[members_].[MemberID_], [Expr1006] NULL PLAN_ROW -1 1.0
|--Nested Loops(Left Outer Join,
WHERE:([members_].[MemberID_]=[lyrActiveRecips].[MemberID])) 11 20 19
Nested Loops Left Outer Join
WHERE:([members_].[MemberID_]=[lyrActiveRecips].[MemberID]) NULL 4782883.5
0.0 9.9962263 19 58.619591 [members_].[MemberID_], [Expr1006]
NULL PLAN_ROW -1 1.0
|--Clustered Index
Seek(OBJECT:([lmdb].[dbo].[members_].[IX_members_List_EmailLC]),
SEEK:([members_].[List_]='list1'), WHERE:([members_].[MemberType_]='normal' AND
[members_].[SubType_]='mail') ORDERED FORWARD) 11 22 20 Clustered
Index Seek Clustered Index Seek
OBJECT:([lmdb].[dbo].[members_].[IX_members_List_EmailLC]),
SEEK:([members_].[List_]='list1'), WHERE:([members_].[MemberType_]='normal' AND
[members_].[SubType_]='mail') ORDERED FORWARD [members_].[SubType_],
[members_].[MemberType_], [members_].[MemberID_] 4782883.5 40.160122
3.2745986 410 43.434719 [members_].[SubType_],
[members_].[MemberType_], [members_].[MemberID_] NULL PLAN_ROW -1 1.0
|--Table Spool 11 24 20 Table Spool
Lazy Spool NULL NULL 1.0 1.6756756E-2 3.7999999E-7 15
0.90211391 [lyrActiveRecips].[MemberID], [Expr1006] NULL PLAN_ROW
-1 4782883.5
|--Compute
Scalar(DEFINE:([Expr1006]=Convert([Expr1021]))) 11 25 24 Compute
Scalar Compute Scalar DEFINE:([Expr1006]=Convert([Expr1021]))
[Expr1006]=Convert([Expr1021]) 1.0 0.0 7.6000001E-6 15 2.4437904E-2
[lyrActiveRecips].[MemberID], [Expr1006] NULL PLAN_ROW -1 1.0
|--Stream Aggregate(GROUP
BY:([lyrActiveRecips].[MemberID]) DEFINE:([Expr1021]=Count(*))) 11 26 25
Stream Aggregate Aggregate GROUP BY:([lyrActiveRecips].[MemberID])
[Expr1021]=Count(*) 1.0 0.0 7.6000001E-6 15 2.4437904E-2
[lyrActiveRecips].[MemberID], [Expr1021] NULL PLAN_ROW -1 1.0
|--Sort(ORDER
BY:([lyrActiveRecips].[MemberID] ASC)) 11 27 26 Sort Sort ORDER
BY:([lyrActiveRecips].[MemberID] ASC) NULL 1.0 1.1261261E-2 1.00011E-4
11 2.4430305E-2 [lyrActiveRecips].[MemberID] NULL PLAN_ROW -1
1.0
|--Filter(WHERE:([outmail_].[Type_]='list')) 11 28 27 Filter
Filter WHERE:([outmail_].[Type_]='list') NULL 1.0 0.0 4.7999998E-7
156 1.3069032E-2 [lyrActiveRecips].[MemberID] NULL PLAN_ROW -1
1.0
|--Bookmark
Lookup(BOOKMARK:([Bmk1004]), OBJECT:([lmdb].[dbo].[outmail_])) 11 29 28
Bookmark Lookup Bookmark Lookup BOOKMARK:([Bmk1004]),
OBJECT:([lmdb].[dbo].[outmail_]) [outmail_].[Type_] 1.0 3.1249749E-3
0.0000011 156 1.3068552E-2 [lyrActiveRecips].[MemberID],
[outmail_].[Type_] NULL PLAN_ROW -1 1.0
|--Nested Loops(Inner Join,
OUTER REFERENCES:([lyrActiveRecips].[MailingID])) 11 30 29 Nested
Loops Inner Join OUTER REFERENCES:([lyrActiveRecips].[MailingID]) NULL
1.0 0.0 0.00001254 138 9.9424766E-3
[lyrActiveRecips].[MemberID], [Bmk1004] NULL PLAN_ROW -1 1.0
|--Bookmark
Lookup(BOOKMARK:([Bmk1002]), OBJECT:([lmdb].[dbo].[lyrActiveRecips])) 11 31
30 Bookmark Lookup Bookmark Lookup BOOKMARK:([Bmk1002]),
OBJECT:([lmdb].[dbo].[lyrActiveRecips]) [lyrActiveRecips].[MemberID],
[lyrActiveRecips].[MailingID] 1.0 3.1249749E-3 0.0000011 53
6.4091529E-3 [lyrActiveRecips].[MemberID], [lyrActiveRecips].[MailingID] NULL
PLAN_ROW -1 1.0
| |--Index
Seek(OBJECT:([lmdb].[dbo].[lyrActiveRecips].[jacktest_lar_date_ix]),
SEEK:([lyrActiveRecips].[NextAttempt] > 'Jan 20 2004 12:00AM') ORDERED FORWARD)
11 32 31 Index Seek Index Seek
OBJECT:([lmdb].[dbo].[lyrActiveRecips].[jacktest_lar_date_ix]),
SEEK:([lyrActiveRecips].[NextAttempt] > 'Jan 20 2004 12:00AM') ORDERED FORWARD
[Bmk1002] 1.0 3.2034749E-3 7.9603E-5 40 3.2830781E-3
[Bmk1002] NULL PLAN_ROW -1 1.0
|--Index
Seek(OBJECT:([lmdb].[dbo].[outmail_].[PK_outmail_]),
SEEK:([outmail_].[MessageID_]=[lyrActiveRecips].[MailingID]) ORDERED FORWARD) 11
33 30 Index Seek Index Seek
OBJECT:([lmdb].[dbo].[outmail_].[PK_outmail_]),
SEEK:([outmail_].[MessageID_]=[lyrActiveRecips].[MailingID]) ORDERED FORWARD
[Bmk1004] 1.0 3.2034749E-3 7.9603E-5 93 3.520784E-3
[Bmk1004] NULL PLAN_ROW -1 3.0
I still can't make heads or tails of it though.
--
greg
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
