Increase performance of a UNION query that thakes 655.07 msec to be runned ? - Mailing list 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