Skip site navigation (1) Skip section navigation (2)

Re: query optimization question

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Jack Coates <jack(at)lyris(dot)com>,pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: query optimization question
Date: 2004-01-30 10:00:58
Message-ID: 87k739ycv9.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Jack Coates <jack(at)lyris(dot)com> 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


In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2004-01-30 14:51:10
Subject: Re: query optimization question
Previous:From: Greg StarkDate: 2004-01-30 09:56:22
Subject: Re: limit 1 and functional indexes: SOLVED

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group