improving performance of UNION and ORDER BY - Mailing list pgsql-general

Three tables...

 Table "a_trans_log"
     Attribute     |           Type           | Modifier
-------------------+--------------------------+----------------------
 acctnum           | character varying(50)    |
 username          | character varying(50)    |
 completed         | timestamp with time zone |
 trans_date        | timestamp with time zone |
 id                | integer                  | not null default nextval
('a_id_seq'::text)
 v_val             | real                     |
 pgnum             | integer                  |
 trans_type        | character varying(50)    |
 trans_data        | character varying(50)    |
 user_reference_id | character varying(50)    |
 trans_charge      | money                    |
Indices: a_date_type_idx,
         a_trans_log_pkey,
         a_type_date_name_idx,
         a_username_idx

 Table "b_trans_log"
     Attribute     |           Type           | Modifier
-------------------+--------------------------+---------------------
 acctnum           | character varying(50)    |
 username          | character varying(50)    |
 completed         | timestamp with time zone |
 trans_date        | timestamp with time zone |
 id                | integer                  | not null default
nextval('b_id_seq'::text)
 trans_type        | character varying(50)    |
 trans_data        | character varying(50)    |
 user_reference_id | character varying(50)    |
 trans_charge      | money                    |
Indices: b_date_type_idx,
         b_trans_log_pkey,
         b_type_date_name_idx,
         b_username_idx

 Table "addtypelong"
 Attribute |         Type          |  Modifier
-----------+-----------------------+--------------------------
 id        | integer               | not null default
nextval('addtypelong_pkey_id'::text)
 shorttype | character varying(50) |
 longtype  | character varying(50) |
Index: addtypelong_pkey

The Query...

select
  a.username as "User",
  a.trans_date as "Date",
  tl.longtype as "Type",
  a.trans_data as "Query Data",
  a.trans_charge as "Charged",
  a.user_reference_id as "Reference ID"
from a_trans_log a, addtypelong tl
where (
  (tl.shorttype=a.trans_type) and
  (a.trans_date >= '12/31/01'::TIMESTAMP)
)
union
select
  b.username as "User",
  b.trans_date as "Date",
  tl.longtype as "Type",
  b.trans_data as "Query Data",
  b.trans_charge as "Charged",
  b.user_reference_id as "Reference ID"
from b_trans_log b, addtypelong tl
where (
  (tl.shorttype=b.trans_type) and
  (b.trans_date >= '12/31/01'::TIMESTAMP)
)
order by 2 desc, 4 limit 20;

The plan...


Limit  (cost=13349.87..13349.87 rows=20 width=84)
  ->  Sort  (cost=13349.87..13349.87 rows=2619 width=84)
        ->  Unique  (cost=12808.41..13201.20 rows=2619 width=84)
              ->  Sort  (cost=12808.41..12808.41 rows=26186 width=84)
                    ->  Append  (cost=1.20..10153.39 rows=26186 width=84)
                          ->  Subquery Scan *SELECT* 1  (cost=1.20..9674.89
rows=23724 width=84)
                                ->  Hash Join  (cost=1.20..9674.89 rows=23724
width=84)
                                      ->  Seq Scan on pubacs_trans_log a
(cost=0.00..8695.30 rows=24455 width=60)
                                      ->  Hash  (cost=1.16..1.16 rows=16
width=24)
                                            ->  Seq Scan on addtypelong tl
(cost=0.00..1.16 rows=16 width=24)
                          ->  Subquery Scan *SELECT* 2  (cost=1.20..478.50
rows=2462 width=84)
                                ->  Hash Join  (cost=1.20..478.50 rows=2462
width=84)
                                      ->  Seq Scan on mvr_trans_log b
(cost=0.00..378.61 rows=2462 width=60)
                                      ->  Hash  (cost=1.16..1.16 rows=16
width=24)
                                            ->  Seq Scan on addtypelong tl
(cost=0.00..1.16 rows=16 width=24)

EXPLAIN

I imagine the combination of UNION and ORDER BY causes the problem, since
Postgres has to locate all the rows that match the search criteria, merge them,
order them, then return the top 20...

Any suggestions? Did I forget to provide any data that would make things
clearer?

BTW: VACUUM ANALYZE is run nightly.

__________________________________________________
Do You Yahoo!?
Yahoo! Greetings - Send FREE e-cards for every occasion!
http://greetings.yahoo.com