pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
help with query
5+ messages / 3 participants
[nested] [flat]

* help with query
@ 2004-08-19 13:17  Dave Cramer <pg@fastcrypt.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Dave Cramer @ 2004-08-19 13:17 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: help with query
@ 2004-08-19 13:38  Brad Bulger <brad@madfish.com>
  parent: Dave Cramer <pg@fastcrypt.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Brad Bulger @ 2004-08-19 13:38 UTC (permalink / raw)
  To: pg@fastcrypt.com; +Cc: pgsql-performance

You're doing a join except not, is the trouble, looks like. The query is really
"FROM Tickets main, Links", but when Tickets.id is 17417, you've got no join
to the Links table. So you end up getting every row in Links for each row in
Tickets with id = 17417.

I'd think this wants to be two queries or a union:

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 ( (main.id = '17417'))
union
SELECT distinct main.oid,main.* FROM Tickets main, Links
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 (main.id = Links.LocalTarget) )
;

or else, yah, a subquery:

[...]
AND (
  main.id = '17417'
  or
  exists(
    select true from Links
    where Type = 'MemberOf' and LocalTarget = '17417'
     and (LocalBase = main.id or LocalTarget = main.id)
  )
)

Those are the only things I can think of to make it work, anyways.

Dave Cramer wrote:

> 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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: help with query
@ 2004-08-19 13:50  Dave Cramer <pg@fastcrypt.com>
  parent: Brad Bulger <brad@madfish.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Dave Cramer @ 2004-08-19 13:50 UTC (permalink / raw)
  To: Brad Bulger <brad@madfish.com>; +Cc: pgsql-performance

Brad,

Thanks, that runs on the same order of magnitude as the subqueries.

DAve
On Thu, 2004-08-19 at 09:38, Brad Bulger wrote:
> You're doing a join except not, is the trouble, looks like. The query is really
> "FROM Tickets main, Links", but when Tickets.id is 17417, you've got no join
> to the Links table. So you end up getting every row in Links for each row in
> Tickets with id = 17417.
> 
> I'd think this wants to be two queries or a union:
> 
> 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 ( (main.id = '17417'))
> union
> SELECT distinct main.oid,main.* FROM Tickets main, Links
> 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 (main.id = Links.LocalTarget) )
> ;
> 
> or else, yah, a subquery:
> 
> [...]
> AND (
>   main.id = '17417'
>   or
>   exists(
>     select true from Links
>     where Type = 'MemberOf' and LocalTarget = '17417'
>      and (LocalBase = main.id or LocalTarget = main.id)
>   )
> )
> 
> Those are the only things I can think of to make it work, anyways.
> 
> Dave Cramer wrote:
> 
> > 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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: help with query
@ 2004-08-19 14:17  Dave Cramer <pg@fastcrypt.com>
  parent: Brad Bulger <brad@madfish.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Dave Cramer @ 2004-08-19 14:17 UTC (permalink / raw)
  To: Brad Bulger <brad@madfish.com>; +Cc: pgsql-performance

From what I can figure, queries like this run much quicker on other
databases, is this something that can be improved ?

Dave
On Thu, 2004-08-19 at 09:38, Brad Bulger wrote:
> You're doing a join except not, is the trouble, looks like. The query is really
> "FROM Tickets main, Links", but when Tickets.id is 17417, you've got no join
> to the Links table. So you end up getting every row in Links for each row in
> Tickets with id = 17417.
> 
> I'd think this wants to be two queries or a union:
> 
> 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 ( (main.id = '17417'))
> union
> SELECT distinct main.oid,main.* FROM Tickets main, Links
> 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 (main.id = Links.LocalTarget) )
> ;
> 
> or else, yah, a subquery:
> 
> [...]
> AND (
>   main.id = '17417'
>   or
>   exists(
>     select true from Links
>     where Type = 'MemberOf' and LocalTarget = '17417'
>      and (LocalBase = main.id or LocalTarget = main.id)
>   )
> )
> 
> Those are the only things I can think of to make it work, anyways.
> 
> Dave Cramer wrote:
> 
> > 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
> 
-- 
Dave Cramer
519 939 0336
ICQ # 14675561




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: help with query
@ 2004-08-19 14:22  Jean-Luc Lachance <jllachan@sympatico.ca>
  parent: Dave Cramer <pg@fastcrypt.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Jean-Luc Lachance @ 2004-08-19 14:22 UTC (permalink / raw)
  To: pg@fastcrypt.com; +Cc: Brad Bulger <brad@madfish.com>; pgsql-performance

how about:

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 ( main.id = '17417'
   OR main.id IN (
     SELECT DISTINCT LocalTarget from Links
     where Type = 'MemberOf' and LocalTarget = '17417')
   OR main.id IN (
     SELECT DISTINCT LocalBase from Links
     where Type = 'MemberOf' and LocalTarget = '17417'))




Dave Cramer wrote:

> Brad,
> 
> Thanks, that runs on the same order of magnitude as the subqueries.
> 
> DAve
> On Thu, 2004-08-19 at 09:38, Brad Bulger wrote:
> 
>>You're doing a join except not, is the trouble, looks like. The query is really
>>"FROM Tickets main, Links", but when Tickets.id is 17417, you've got no join
>>to the Links table. So you end up getting every row in Links for each row in
>>Tickets with id = 17417.
>>
>>I'd think this wants to be two queries or a union:
>>
>>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 ( (main.id = '17417'))
>>union
>>SELECT distinct main.oid,main.* FROM Tickets main, Links
>>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 (main.id = Links.LocalTarget) )
>>;
>>
>>or else, yah, a subquery:
>>
>>[...]
>>AND (
>>  main.id = '17417'
>>  or
>>  exists(
>>    select true from Links
>>    where Type = 'MemberOf' and LocalTarget = '17417'
>>     and (LocalBase = main.id or LocalTarget = main.id)
>>  )
>>)
>>
>>Those are the only things I can think of to make it work, anyways.
>>
>>Dave Cramer wrote:
>>
>>
>>>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
>>



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2004-08-19 14:22 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-19 13:17 help with query Dave Cramer <pg@fastcrypt.com>
2004-08-19 13:38 ` Brad Bulger <brad@madfish.com>
2004-08-19 13:50   ` Dave Cramer <pg@fastcrypt.com>
2004-08-19 14:22     ` Jean-Luc Lachance <jllachan@sympatico.ca>
2004-08-19 14:17   ` Dave Cramer <pg@fastcrypt.com>

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