From: Dave Cramer <pg@fastcrypt.com>
To: perform <pgsql-performance@postgresql.org>
Subject: help with query
Date: Thu, 19 Aug 2004 09:17:13 -0400
Message-ID: <1092921433.1622.569.camel@localhost.localdomain> (raw)
RT uses a query like:
SELECT distinct main.oid,main.* FROM Tickets main
WHERE
(main.EffectiveId = main.id)
AND
(main.Status != 'deleted')
AND
( (main.Type = 'ticket') OR (main.Type = 'subticket') )
AND
( (main.Queue = '9') )
AND ((
( (Links.Type = 'MemberOf') AND (Links.LocalTarget = '17417') AND (main.id = Links.LocalBase) )
OR
( (Links.Type = 'MemberOf') AND (Links.LocalBase = '17417') AND (main.id = Links.LocalTarget) )
or
(main.id = '17417')
)
);
which produces a query plan:
Nested Loop (cost=0.00..813.88 rows=1 width=169)
Join Filter: (((("inner"."type")::text = 'MemberOf'::text) OR ("outer".id = 17417)) AND (("inner".localbase = 17417) OR (("inner"."type")::text = 'MemberOf'::text) OR ("outer".id
= 17417)) AND (("outer".id = "inner".localtarget) OR (("inner"."type")::text = 'MemberOf'::text) OR ("outer".id = 17417)) AND ((("inner"."type")::text = 'MemberOf'::text) OR ("inner"
.localtarget = 17417) OR ("outer".id = 17417)) AND (("inner".localbase = 17417) OR ("inner".localtarget = 17417) OR ("outer".id = 17417)) AND (("outer".id = "inner".localtarget) OR (
"inner".localtarget = 17417) OR ("outer".id = 17417)) AND ((("inner"."type")::text = 'MemberOf'::text) OR ("outer".id = "inner".localbase) OR ("outer".id = 17417)) AND (("inner".loca
lbase = 17417) OR ("outer".id = "inner".localbase) OR ("outer".id = 17417)) AND (("outer".id = "inner".localtarget) OR ("outer".id = "inner".localbase) OR ("outer".id = 17417)))
-> Index Scan using tickets1 on tickets main (cost=0.00..657.61 rows=1 width=169)
Index Cond: (queue = 9)
Filter: ((effectiveid = id) AND ((status)::text <> 'deleted'::text) AND ((("type")::text = 'ticket'::text) OR (("type")::text = 'subticket'::text)))
-> Seq Scan on links (cost=0.00..46.62 rows=1462 width=20)
If I rewrite the query as:
SELECT main.* FROM Tickets main
WHERE
(main.EffectiveId = main.id)
AND
(main.Status != 'deleted')
AND
( (main.Type = 'ticket') OR (main.Type = 'subticket') )
AND
( (main.Queue = '9') )
AND (
17417 in (select links.localtarget from links where links.type='MemberOf' and main.id=links.localbase)
or
17417 in ( select links.localbase from links where links.type='MemberOf' and main.id=links.localtarget)
or
main.id = '17417'
)
;
The time for the query goes from 1500ms to 15ms. The two OR clauses
( (Links.Type = 'MemberOf') AND (Links.LocalTarget = '17417') AND (main.id = Links.LocalBase) )
OR
( (Links.Type = 'MemberOf') AND (Links.LocalBase = '17417') AND (main.id = Links.LocalTarget) )
don't contribute to the result set in this particular dataset, which is why the speed increases so dramatically.
Is there a way to rewrite the top query to get the same results? I have already talked to Best Practical,
and subqueries are not easily embraced.
Dave
--
Dave Cramer
519 939 0336
ICQ # 14675561
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pg@fastcrypt.com
Subject: Re: help with query
In-Reply-To: <1092921433.1622.569.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox