RT with PostgreSQL .

[Rajesh Kumar Mallah]

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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Andrew Sullivan]

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
<an****@libertyrms.info> M2P 2A8
[phone] x110
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org