Re: query optimization question

On Thu, 2004-01-29 at 11:31, Tom Lane wrote:
> Jack Coates <jack(at)lyris(dot)com> writes:
> > jackdb=# explain SELECT DISTINCT members_.memberid_
> > jackdb-# FROM members_
> > jackdb-# WHERE ( members_.List_='list1'
> > jackdb(# AND members_.MemberType_='normal'
> > jackdb(# AND members_.SubType_='mail'
> > jackdb(# AND members_.emailaddr_ IS NOT NULL )
> > jackdb-# GROUP BY memberid_ HAVING (
>
> Um, that's not what I had in mind at all. Does GROUP BY actually do
> anything at all here? (You didn't answer me as to whether memberid_
> is a unique identifier or not, but if it is, this GROUP BY is just an
> expensive no-op.)
>

Sorry for the misunderstanding. It should be unique, yes.

> What I was envisioning was pulling the sub-selects up to the top level
> and using grouping to calculate the count(*) values for all memberids
> in parallel. Roughly speaking it would look like (again assuming
> memberid_ is unique)
>
> SELECT memberid_ FROM
> (
> SELECT memberid_ FROM lyrActiveRecips, members_, outmail
> WHERE (all the conditions for this case)
> UNION ALL
> SELECT memberid_ FROM lyrCompletedRecips, members_, outmail
> WHERE (all the conditions for this case)
> )
> GROUP BY memberid_ HAVING count(*) = 3;
>
> However, if you can't change the boilerplate part of your query then
> this is all blue-sky speculation anyway.

Got it now -- I'm running into some subquery errors trying to implement
this, anyway.

> What I'm actually more
> interested in is your statement that MSSQL can do the original query
> quickly. I find that a bit hard to believe because I don't see any
> relevant optimization techniques. Do they have any equivalent to
> EXPLAIN that would give some hint how they're doing it?

yup -- here it is. It will probably be a nasty mess after linewrap gets
done with it, so let me know if you'd like me to post a copy on ftp.

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
--
Jack Coates, Lyris Technologies Applications Engineer
510-549-4350 x148, jack(at)lyris(dot)com
"Interoperability is the keyword, uniformity is a dead end."
--Olivier Fourdan