From: Richard Huxton <dev@archonet.com>
To: Peter Darley <pdarley@kinesis-cem.com>
Cc: Pgsql-Performance <pgsql-performance@postgresql.org>
Subject: Re: Possibly slow query
Date: Wed, 26 Jan 2005 09:36:02 +0000
Message-ID: <41F76482.7080001@archonet.com> (raw)
In-Reply-To: <PDEOIIFFBIAABMGNJAGPGEPJDLAA.pdarley@kinesis-cem.com>
References: <PDEOIIFFBIAABMGNJAGPGEPJDLAA.pdarley@kinesis-cem.com>
Peter Darley wrote:
> Folks,
>
> I'm using PostgreSQL 7.4.1 on Linux, and I'm trying to figure out weather a
> query I have is going to be slow when I have more information in my tables.
> both tables involved will likely have ~500K rows within a year or so.
>
> Specifically I can't tell if I'm causing myself future problems with the
> subquery, and should maybe re-write the query to use a join. The reason I
> went with the subquery is that I don't know weather a row in Assignments
> will have a corresponding row in Assignment_Settings
>
> The query is:
> SELECT User_ID
> FROM Assignments A
> WHERE A.User_ID IS NOT NULL
> AND (SELECT Value FROM Assignment_Settings WHERE Setting='Status' AND
> Assignment_ID=A.Assignment_ID) IS NULL
> GROUP BY User_ID;
You could always use a LEFT JOIN instead, like you say. I'd personally
be tempted to select distinct user_id's then join, but it depends on how
many of each.
You're not going to know for sure whether you'll have problems without
testing. Generate 500k rows of plausible looking test-data and give it a
try.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, pdarley@kinesis-cem.com
Subject: Re: Possibly slow query
In-Reply-To: <41F76482.7080001@archonet.com>
* 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