Dear Folks,
The SQLs from RT (2-0-9 version) (http://www.fsck.com/)
are totally brain dead. A simple tweek makes some of the queries
2840 TIMES faster. Dunno if recent versions of RT (3-x-x) takes care of
PostgreSQL.
I had always been wondering why my RT it is sooo slow , today i know.
since TransactionId and parent are both ints i cant make index on lower(int)
rt2=# explain analyze SELECT DISTINCT main.* FROM Attachments main WHERE
((lower(main.TransactionId) = '104120')) AND ((lower(main.Parent) = '0'));
NOTICE: QUERY PLAN:
Unique (cost=16167.15..16167.25 rows=1 width=1084) (actual
time=1429.61..1429.62 rows=1 loops=1)
-> Sort (cost=16167.15..16167.15 rows=3 width=1084) (actual
time=1429.59..1429.59 rows=1 loops=1)
-> Seq Scan on attachments main (cost=0.00..16167.12 rows=3
width=1084) (actual time=1098.08..1429.26 rows=1 loops=1)
Total runtime: 1429.75 msec
EXPLAIN
rt2=# explain analyze SELECT DISTINCT main.* FROM Attachments main WHERE
TransactionId = 104120 AND main.Parent = '0';
NOTICE: QUERY PLAN:
Unique (cost=7.91..7.96 rows=1 width=1084) (actual time=0.68..0.69 rows=1
loops=1)
-> Sort (cost=7.91..7.91 rows=2 width=1084) (actual time=0.68..0.68 rows=1
loops=1)
-> Index Scan using attachments3 on attachments main
(cost=0.00..7.90 rows=2 width=1084) (actual time=0.31..0.32 rows=1 loops=1)
Total runtime: 0.82 msec
EXPLAIN
rt2=#
rt2=# \d Attachments
Table "attachments"
Column | Type | Modifiers
-----------------+--------------------------+--------------------------------------------------------
id | integer | not null default
nextval('"attachments_id_seq"'::text)
transactionid | integer | not null
parent | integer |
messageid | character varying(160) |
subject | character varying(255) |
filename | character varying(255) |
contenttype | character varying(80) |
contentencoding | character varying(80) |
content | text |
headers | text |
creator | integer |
created | timestamp with time zone |
Indexes: attachments1,
attachments2,
attachments3
Primary key: attachments_pkey