From: Tomasz Myrta <jasiek@klaster.net>
To: James Cooper <jim@luckydigital.com>
To: sql <pgsql-sql@postgresql.org>
Subject: Re: INTERSECT / where id IN (etc..)
Date: Thu, 6 Mar 2003 07:39:53 +0800
Message-ID: <20030306073953.M77564@klaster.net> (raw)
In-Reply-To: <003701c2e37a$fe0bb2d0$c900a8c0@jax>
References: <003701c2e37a$fe0bb2d0$c900a8c0@jax>
> 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
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-sql@postgresql.org
Cc: jasiek@klaster.net, jim@luckydigital.com
Subject: Re: INTERSECT / where id IN (etc..)
In-Reply-To: <20030306073953.M77564@klaster.net>
* 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