RE : Increase performance of a UNION query that thakes 655.07 msec to be runned ?
От | Bruno BAGUETTE |
---|---|
Тема | RE : Increase performance of a UNION query that thakes 655.07 msec to be runned ? |
Дата | |
Msg-id | !~!UENERkVCMDkAAQACAAAAAAAAAAAAAAAAABgAAAAAAAAATBuXKOMvlkWzD3KJN6FWLMKAAAAQAAAATTiiv7GdnUSKhqnNLY4+3gEAAAAA@baguette.net обсуждение исходный текст |
Ответ на | Re: Increase performance of a UNION query that thakes 655.07 msec to be runned ? (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-performance |
re-Hello, As suggested by Tom, I've removed the distinct and tried it's query : levure=> explain analyze select initiale from ( levure(> select lower(substr(l_name,1,1)) as initiale from people levure(> union all levure(> select lower(substr(org_name,1,1)) as initiale from organizations levure(> ) ss levure-> group by initiale order by initiale; QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------------------------ - Group (cost=1018.48..1074.32 rows=1117 width=17) (actual time=783.47..867.61 rows=39 loops=1) -> Sort (cost=1018.48..1046.40 rows=11167 width=17) (actual time=782.18..801.68 rows=11167 loops=1) Sort Key: initiale -> Subquery Scan ss (cost=0.00..267.67 rows=11167 width=17) (actual time=0.23..330.31 rows=11167 loops=1) -> Append (cost=0.00..267.67 rows=11167 width=17) (actual time=0.22..263.69 rows=11167 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..87.93 rows=4093 width=15) (actual time=0.22..79.51 rows=4093 loops=1) -> Seq Scan on people (cost=0.00..87.93 rows=4093 width=15) (actual time=0.20..53.82 rows=4093 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..179.74 rows=7074 width=17) (actual time=0.24..146.12 rows=7074 loops=1) -> Seq Scan on organizations (cost=0.00..179.74 rows=7074 width=17) (actual time=0.23..100.70 rows=7074 loops=1) Total runtime: 874.79 msec (10 rows) That seems to be 200 msec longer that my first query... Indeed, I've noticed something strange : now, if I rerun my initial query, I get worse runtime than this morning : levure=> EXPLAIN ANALYZE SELECT lower(substr(l_name, 1, 1)) AS initiale FROM people levure-> UNION levure-> SELECT lower(substr(org_name, 1, 1)) AS initiale FROM organizations levure-> ORDER BY initiale; QUERY PLAN ------------------------------------------------------------------------ ------------------------------------------------------------------------ Sort (cost=1130.85..1133.64 rows=1117 width=17) (actual time=802.52..802.58 rows=39 loops=1) Sort Key: initiale -> Unique (cost=1018.48..1074.32 rows=1117 width=17) (actual time=712.04..801.83 rows=39 loops=1) -> Sort (cost=1018.48..1046.40 rows=11167 width=17) (actual time=712.03..732.63 rows=11167 loops=1) Sort Key: initiale -> Append (cost=0.00..267.67 rows=11167 width=17) (actual time=0.21..263.54 rows=11167 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..87.93 rows=4093 width=15) (actual time=0.20..80.47 rows=4093 loops=1) -> Seq Scan on people (cost=0.00..87.93 rows=4093 width=15) (actual time=0.19..54.14 rows=4093 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..179.74 rows=7074 width=17) (actual time=0.28..144.82 rows=7074 loops=1) -> Seq Scan on organizations (cost=0.00..179.74 rows=7074 width=17) (actual time=0.27..99.06 rows=7074 loops=1) Total runtime: 806.47 msec (11 rows) I don't understand why this runtime has changed because no data has been added/updated/deleted since several weeks (I'm working on a copy of the production database. And this copy is not accessible for the users). My PostgreSQL version is PostgreSQL 7.3.2, I have to ask to the administrator if it can be upgraded to 7.4 in the production server. Thanks in advance for your help. --------------------------------------- Bruno BAGUETTE - pgsql-ml@baguette.net
В списке pgsql-performance по дате отправления:
Следующее
От: "Bruno BAGUETTE"Дата:
Сообщение: RE : Increase performance of a UNION query that thakes 655.07 msec to be runned ?