Re: What to index to speed up my UNION views?

On Tue, Mar 28, 2006 at 09:45:02AM +0100, Ashley Moran wrote:
> On Monday 27 March 2006 12:13, Martijn van Oosterhout wrote:
> > You're using UNION rather than UNION ALL where. There's a big
> > difference and I imagine you actually want the latter. It also makes a
> > big difference in query optimisation.
> >
> > If that doesn't fix it, come back with the EXPLAIN ANALYZE output of
> > your query.
> >
> > Have a nice day,
>
> Thanks Martin
>
> It turned out that the query being used was wrong anyway (pulling through too
> much data). When it was fixed it sped up greatly. I've searched on the
> Postgres docs and can't find an explanation of UNION ALL. How does it differ
> from UNION?

That's because it's decribed in the SQL standard. UNION ALL just joins
the results of the two queries. UNION removes duplicates which usually
means sorting and comparing the tuples. UNION ALL is faster and usually
what you want anyway...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.