By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,373 Members | 1,984 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,373 IT Pros & Developers. It's quick & easy.

RT with PostgreSQL .

P: n/a

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

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
+1 416 646 3304 x110
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.