pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIncrease performance of a UNION query that thakes 655.07 msec to be runned ?
3+ messages / 3 participants
[nested] [flat]
* Increase performance of a UNION query that thakes 655.07 msec to be runned ?
@ 2004-02-06 08:58 Bruno BAGUETTE <pgsql-ml@baguette.net>
2004-02-06 15:28 ` Re: Increase performance of a UNION query that thakes 655.07 msec to be runned ? Tom Lane <tgl@sss.pgh.pa.us>
2004-02-06 15:52 ` Re: Increase performance of a UNION query that thakes Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 3+ messages in thread
From: Bruno BAGUETTE @ 2004-02-06 08:58 UTC (permalink / raw)
To: pgsql-performance
Hello,
Do you see a way to get better performances with this query which takes
currently 655.07 msec to be done.
levure=> explain analyze SELECT distinct lower(substr(l_name, 1, 1)) AS
initiale FROM people
levure-> UNION
levure-> SELECT distinct lower(substr(org_name, 1, 1)) AS initiale FROM
organizations
levure-> ORDER BY initiale;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
-----------
Sort (cost=158.73..158.78 rows=20 width=43) (actual
time=650.82..650.89 rows=39 loops=1)
Sort Key: initiale
-> Unique (cost=157.30..158.30 rows=20 width=43) (actual
time=649.55..650.17 rows=39 loops=1)
-> Sort (cost=157.30..157.80 rows=200 width=43) (actual
time=649.55..649.67 rows=69 loops=1)
Sort Key: initiale
-> Append (cost=69.83..149.66 rows=200 width=43)
(actual time=198.48..648.51 rows=69 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=69.83..74.83
rows=100 width=38) (actual time=198.48..230.62 rows=37 loops=1)
-> Unique (cost=69.83..74.83 rows=100
width=38) (actual time=198.46..230.31 rows=37 loops=1)
-> Sort (cost=69.83..72.33 rows=1000
width=38) (actual time=198.45..205.99 rows=4093 loops=1)
Sort Key:
lower(substr((l_name)::text, 1, 1))
-> Seq Scan on people
(cost=0.00..20.00 rows=1000 width=38) (actual time=0.19..52.33 rows=4093
loops=1)
-> Subquery Scan "*SELECT* 2" (cost=69.83..74.83
rows=100 width=43) (actual time=361.82..417.62 rows=32 loops=1)
-> Unique (cost=69.83..74.83 rows=100
width=43) (actual time=361.79..417.33 rows=32 loops=1)
-> Sort (cost=69.83..72.33 rows=1000
width=43) (actual time=361.79..374.81 rows=7074 loops=1)
Sort Key:
lower(substr((org_name)::text, 1, 1))
-> Seq Scan on organizations
(cost=0.00..20.00 rows=1000 width=43) (actual time=0.23..95.47 rows=7074
loops=1)
Total runtime: 655.07 msec
(17 rows)
I was thinking that a index on lower(substr(l_name, 1, 1)) and another
index on lower(substr(org_name, 1, 1)) should gives better performances.
When I've to create theses two indexes, it seems like this is not
allowed :
levure=> CREATE INDEX firstchar_lastname_idx ON
people(lower(substr(l_name, 1, 1)));
ERROR: parser: parse error at or near "(" at character 59
Do you have another idea to get better performances ?
Thanks in advance :-)
PS : Note that this database is VACUUMed twice per day (and sometimes
more).
-------------------------------------
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 08:58 Increase performance of a UNION query that thakes 655.07 msec to be runned ? Bruno BAGUETTE <pgsql-ml@baguette.net>
@ 2004-02-06 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2004-02-06 15:28 UTC (permalink / raw)
To: Bruno BAGUETTE <pgsql-ml@baguette.net>; +Cc: pgsql-performance
"Bruno BAGUETTE" <pgsql-ml@baguette.net> writes:
> Do you see a way to get better performances with this query which takes
> currently 655.07 msec to be done.
> levure=> explain analyze SELECT distinct lower(substr(l_name, 1, 1)) AS
> initiale FROM people
> levure-> UNION
> levure-> SELECT distinct lower(substr(org_name, 1, 1)) AS initiale FROM
> organizations
> levure-> ORDER BY initiale;
This is inherently a bit inefficient since the UNION implies a DISTINCT
step, thus partially repeating the DISTINCT work done inside each SELECT.
It would likely be a tad faster to drop the DISTINCTs from the
subselects and rely on UNION to do the filtering. However, you're still
gonna have a big SORT/UNIQUE step.
As of PG 7.4 you could probably get a performance win by converting the
thing to use GROUP BY instead of DISTINCT or UNION:
select initiale from (
select lower(substr(l_name,1,1)) as initiale from people
union all
select lower(substr(org_name,1,1)) as initiale from organizations
) ss
group by initiale order by initiale;
This should use a HashAggregate to do the unique-ification. I think
that will be faster than Sort/Unique.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Increase performance of a UNION query that thakes
2004-02-06 08:58 Increase performance of a UNION query that thakes 655.07 msec to be runned ? Bruno BAGUETTE <pgsql-ml@baguette.net>
@ 2004-02-06 15:52 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2004-02-06 15:52 UTC (permalink / raw)
To: Bruno BAGUETTE <pgsql-ml@baguette.net>; +Cc: pgsql-performance
On Fri, 6 Feb 2004, Bruno BAGUETTE wrote:
> I was thinking that a index on lower(substr(l_name, 1, 1)) and another
> index on lower(substr(org_name, 1, 1)) should gives better performances.
> When I've to create theses two indexes, it seems like this is not
> allowed :
>
> levure=> CREATE INDEX firstchar_lastname_idx ON
> people(lower(substr(l_name, 1, 1)));
> ERROR: parser: parse error at or near "(" at character 59
In 7.4, I believe you would say
on people((lower(substr(l_name,1,1))))
but I'm not sure that index would really help in practice.
> Do you have another idea to get better performances ?
In addition to what Tom said, the row estimates look suspiciously default.
You mention vacuuming, but do you ever analyze the tables?
Also, what do you have sort_mem set to?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-02-06 15:52 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 08:58 Increase performance of a UNION query that thakes 655.07 msec to be runned ? Bruno BAGUETTE <pgsql-ml@baguette.net>
2004-02-06 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-06 15:52 ` 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