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 по дате отправления: