Hi.
Speed-wise, is there a signifficant performance difference between doing
complex queries in the following forms?
Form 1:
(
SELECT Master.*
FROM Master,
MasterFTI
WHERE MasterFTI.ID = Master.ID AND
MasterFTI.String = 'string1'
)
UNION
(
SELECT Master.*
FROM Master,
MasterFTI
WHERE MasterFTI.ID = Master.ID AND
MasterFTI.String = 'string2'
)
...
UNION
...;
Form 2:
SELECT DISTINCT Master.*
FROM Master,
MasterFTI
WHERE MasterFTI.ID = Master.ID AND
(
MasterFTI.String = 'string1' OR
MasterFTI.String = 'string2' OR
...
);
The reason am asking is because I don't know how the back end splits and
executes these queries. Are the UNION/INTERSECT/EXCEPT queries each executed
separately in sequence? Or does the optimizer do some magic and transform
them in a more efficient way that doesn't require multiple passes?
And is the overhead of running multiple UNION queries greater than the
overhead of doing a DISTINCT? I need to sort the records anyway, so the fact
that DISTINCT does a SORT is a bonus in this case.
In an extreme case my dynamically constructed queries (from a CGI) can have
as many as 50 terms in them, which using the UNION method, equates to 50
queries being run (if that is the way it all gets executed). Is there likely
to be a sizeable improvement in using the other method? The reason I am
asking before trying is because I'd like to avoid re-writing my custom->SQL
parser again.
I was hoping that someone with a bit more background knowledge into how
PostgreSQL works could shed some light on it...
Regards.
Gordan
