Re: optimising UNION performance

Rafal Pietrak wrote:
> Well. The logfiles don't have their own indexes but make foreign key
> references over brand1/brand2/clty columns. Unique constreins are on the
> target tables.

So there's no index on the logfiles then? (A foreign key constraint
doesn't create an index). It doesn't seem like in your case an index
would cause any benefit, just so you know.

It also means that your problem is different from mine; PostgreSQL using
a seq-scan over a UNION (ALL) where indices were available over the
parts (mind you, it would use the appropriate index on the seperate
union parts).

> But that particual ANALYSED query was: "SELECT 1 FROM comlog".
>
> So, the use of seq-scan looks quite adequate to me (I haven't posted
> results to avoid cuttering of my original query with too many details,
> but the ANALYSE of "SELECT * FROM comlog" gives almost exactly the same
> cost and time, and *that* is what I will actually be doing in the
> application).

There's practically no difference between SELECT 1 FROM ... and SELECT *
FROM ...; the only added costs (AFAIK) are for actually fetching the
column values and such. Pretty cheap operations.

> So: "SELECT 1" on comlog costs 830ms and is done by two saq-scans.
>
> but: "SELECT 1" on log1 gives 120ms, and "SELECT 1" on log2 gives 80ms.
>
> All three queries are executed as seq-scans.... which look OK, as I
> intend to fetch *all* the rows.
>
> And yet, there is this 600ms 'leak'.

...

> 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.

Regards,
--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //