pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: INTERSECT / where id IN (etc..)
2+ messages / 2 participants
[nested] [flat]
* INTERSECT / where id IN (etc..)
@ 2003-03-06 00:54 James Cooper <jim@luckydigital.com>
2003-03-05 23:39 ` Re: INTERSECT / where id IN (etc..) Tomasz Myrta <jasiek@klaster.net>
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
* Re: INTERSECT / where id IN (etc..)
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>
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
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