pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
RE : Increase performance of a UNION query that thakes 655.07 msec to be runned ?
3+ messages / 2 participants
[nested] [flat]

* RE : Increase performance of a UNION query that thakes 655.07 msec to be runned ?
@ 2004-02-06 16:34  Bruno BAGUETTE <pgsql-ml@baguette.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Bruno BAGUETTE @ 2004-02-06 16:34 UTC (permalink / raw)
  To: 'Tom Lane' <tgl@sss.pgh.pa.us>; 'Bruno BAGUETTE' <pgsql-ml@baguette.net>; +Cc: 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 




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* RE : Increase performance of a UNION query that thakes 655.07 msec to be runned ?
@ 2004-02-06 16:41  Bruno BAGUETTE <pgsql-ml@baguette.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Bruno BAGUETTE @ 2004-02-06 16:41 UTC (permalink / raw)
  To: 'Stephan Szabo' <sszabo@megazone.bigpanda.com>; 'Bruno BAGUETTE' <pgsql-ml@baguette.net>; +Cc: pgsql-performance

> In addition to what Tom said, the row estimates look 
> suspiciously default. You mention vacuuming, but do you ever 
> analyze the tables?

I run VACUUM FULL ANALYZE with the postgres user on all the PostgreSQL
databases on the server, twice a day, sometimes more.

> Also, what do you have sort_mem set to?

[root@levure data]# cat postgresql.conf | grep sort_mem
sort_mem = 6144                 # min 64, size in KB 

Do you think I should increase that value ?

It's not so easy to do a good setup of that postgresql.conf file, is
there any tool that suggests some values for that ?

Thanks in advance for your tips :-)

---------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net 




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: RE : Increase performance of a UNION query that thakes
@ 2004-02-06 17:13  Stephan Szabo <sszabo@megazone.bigpanda.com>
  parent: Bruno BAGUETTE <pgsql-ml@baguette.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2004-02-06 17:13 UTC (permalink / raw)
  To: Bruno BAGUETTE <pgsql-ml@baguette.net>; +Cc: pgsql-performance

On Fri, 6 Feb 2004, Bruno BAGUETTE wrote:

> > In addition to what Tom said, the row estimates look
> > suspiciously default. You mention vacuuming, but do you ever
> > analyze the tables?
>
> I run VACUUM FULL ANALYZE with the postgres user on all the PostgreSQL
> databases on the server, twice a day, sometimes more.

Wierd, because you're getting 1000 estimated on both people and
organizations.  What does pg_class have to say about those two tables?

> > Also, what do you have sort_mem set to?
>
> [root@levure data]# cat postgresql.conf | grep sort_mem
> sort_mem = 6144                 # min 64, size in KB
>
> Do you think I should increase that value ?

Hmm, I'd expect that the sort would fit in that space in general.  If you
want to try different values, you can set sort_mem from psql rather than
changing the configuration file.

----

On my machine the index does actually help, although I needed to lower
random_page_cost a little from its default of 4 to get it to use it
preferentially, but I'm also getting times about 1/3 of yours (and my
machine is pretty poor) so I think I may not have data that matches yours
very well.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-02-06 17:13 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-06 16:34 RE : Increase performance of a UNION query that thakes 655.07 msec to be runned ? Bruno BAGUETTE <pgsql-ml@baguette.net>
2004-02-06 16:41 RE : Increase performance of a UNION query that thakes 655.07 msec to be runned ? Bruno BAGUETTE <pgsql-ml@baguette.net>
2004-02-06 17:13 ` Re: RE : Increase performance of a UNION query that thakes Stephan Szabo <sszabo@megazone.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox