Re: Query plan question

Hi Tom,
basically I complaining that PG does not do what I told to do or
was hoping to do.

What I was hopping to do:
join temp table and main table + evaluate all aggregates for small
subset,
in my case subset is not so small 10000 rows and after that join
to other tables that will effectively grow resultset size, due to one to
many
relations.
But what I see PG calculates all aggregates for the final resultset in
my case 10720,
but that could be up to x5 times more.

In order to be more clear let's consider following simplification

4 tables DOCS, REVIEWERS, REVISIONS AND OTHER
[docid] is primary key in DOCS,
DOCS one to many for REVIVERS, REVISIONS, OTHER

I want following output:
[docid], [reviewrs cnt], [revisions cnt], otherfields...

So what could be simpler than

SELECT ....
FROM( SELECT docid, stuff, ....
( SELECT count(...) FROM REVIEWERS ),
( SELECT count(...) FROM REVISIONS )
FROM DOCS
) AS t
LEFT OUTER JOIN OTHER ....

I want result set [t] to be evaluated first.
I reality I have 4/6 aggregate to calc. and 3/4 tables to join.

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, June 25, 2003 7:53 PM
To: Maksim Likharev
Cc: GENERAL
Subject: Re: [GENERAL] Query plan question

"Maksim Likharev" <mlikharev(at)aurigin(dot)com> writes:
> I was trying to force PG do inner join 2 small tables calcs. necessary
> aggregates per id
> and only after that left outer join with the rest.

AFAICS you're contorting your query to force the evaluation order.
Why are you complaining that PG follows what you told it to do?

I'm having a hard time figuring out exactly what the query's intent
is. What does it look like when you express it in the simplest way
possible, with minimum use of subselects?

regards, tom lane