More Performance Questions

Поиск
Список
Период
Сортировка
От Gordan Bobic
Тема More Performance Questions
Дата
Msg-id 200111071622.fA7GMnM05089@sentinel.bobich.net
обсуждение исходный текст
Ответы Re: More Performance Questions
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Bryan White"
Дата:
Сообщение: Re: a query ...
Следующее
От: Jean-Michel POURE
Дата:
Сообщение: Re: functions vs embedded SQL