Обсуждение: RT with PostgreSQL .

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

On Sat, Sep 27, 2003 at 05:01:30AM -0400, Rajesh Kumar Mallah wrote:
>
> 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.

Since RT was actually programmed against MySQL, and only ported to
PostgreSQL, I dunno that I'd call them "brain dead".  Perhaps they
need a little more education is all.  But I bet they'd take your
patch.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110