pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Re: INTERSECT / where id IN (etc..)
2+ messages / 2 participants
[nested] [flat]

* Re: INTERSECT / where id IN (etc..)
@ 2003-03-05 23:39  Tomasz Myrta <jasiek@klaster.net>
  parent: James Cooper <jim@luckydigital.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tomasz Myrta @ 2003-03-05 23:39 UTC (permalink / raw)
  To: James Cooper <jim@luckydigital.com>; pgsql-sql

> Greetings.
> 
> I was wondering if somone out there could shed some light on a query 
> performance issie I have. I have a large lookup table that I query 
> with nested selects to gain the INTERSECT result from.
> 
> the bigger this table gets the slower the performance.
> 
> 1. is creating a temp table and only intersecting from this a good idea?
> 
> 2. does having a where clause in your select before your nested 
> intersects change the amount of work the query
> 
> i.e. select person_id from person where person_id < 5000 And 
> person_id IN    as opposed to    select person_id from person where 
> person_id IN(etc)
> 
> thoughts?
person_id<5000 probably won't help - postgres will use index on exact 
person_id if possible.
Anyway - for large queries try change IN (...) into EXISTS (...), or just 
into explicit join if possible. IN clause is rather slow for bigger amount of 
data.

Regards,
Tomasz Myrta




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

* INTERSECT / where id IN (etc..)
@ 2003-03-06 00:54  James Cooper <jim@luckydigital.com>
  0 siblings, 1 reply; 2+ messages in thread

From: James Cooper @ 2003-03-06 00:54 UTC (permalink / raw)
  To: pgsql-sql

Greetings.

I was wondering if somone out there could shed some light on a query performance issie I have.
I have a large lookup table that I query with nested selects to gain the INTERSECT result from.

the bigger this table gets the slower the performance.

1. is creating a temp table and only intersecting from this a good idea?
2. does having a where clause in your select before your nested intersects change the amount of work the query

i.e. select person_id from person where person_id < 5000 And person_id IN
    as opposed to
    select person_id from person where person_id IN(etc)

thoughts?

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

end of thread, other threads:[~2003-03-06 00:54 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-06 00:54 INTERSECT / where id IN (etc..) James Cooper <jim@luckydigital.com>
2003-03-05 23:39 ` Tomasz Myrta <jasiek@klaster.net>

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