Re: optimising UNION performance

On Mon, 2006-08-28 at 14:50 +0200, Alban Hertroys wrote:
> Rafal Pietrak wrote:
>
> > But when I look at ANALYSE output of comlog SELECT, I can see, that:
> > 1. the seq-scans is more expensive here: 170ms and 120ms respectively.
> > Any reasons for that?
> > 2. each scan has an additional job of: Subquery Scan "*SELECT* 1" ...
> > which costs even more (280ms and 230ms respectively), although it's
> > purpose it not very clear to me.
>
> This is probably caused by using UNION as opposed to UNION ALL (as other
> people already mentioned).
>
> To merge duplicate results (one from either subquery) the database
> sorts[1] the results. To do that, it needs to compare with other records
> - hence the extra subquery, and probably the added 50ms as well.

No no no.

The above 1. 2. 3. is read from UNION ALL analysis - the results of
UNION per se are only in my initial post, and after I've read of the
'ALL' option I make no further reference to the original construct
(where the cost of "SELECT 1" was 3600ms as oposed to 830ms for current
"UNION ALL").

Currently I'm digging why the SELECT on UNION takes 830ms, while SELECT
on respective raw log-tables take just 120ms and 80ms respectively -
where does the remaining 600ms go.

I have notices the spurious "Subquery Scan "*SELECT* 1" ..." server
task, which takes more then the indispensable "seq-scan" on respective
table while does not serve any purpose .... to my unexperienced eye at
least.

And why the same seq-scan taken by select on my log-table *within* a
UNION is more expensive, than when it's taken on that table by itself:
120ms rises to 170ms, and 80ms rises to 120ms for log1/log2 tables
respectively.
--
-R