From: | "Peter Darley" <pdarley(at)kinesis-cem(dot)com> |
---|---|
To: | "Manfred Koizar" <mkoi-pg(at)aon(dot)at> |
Cc: | "Richard Huxton" <dev(at)archonet(dot)com>, "Pgsql-Performance" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Possibly slow query |
Date: | 2005-01-31 15:06:50 |
Message-ID: | PDEOIIFFBIAABMGNJAGPGEAHDMAA.pdarley@kinesis-cem.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Manfred,
Yeah, that was a typo. It should have been ASet.Value IS NULL.
I have considered storing the setting names by key, since I do have a
separate table with the names and a key as you suggest, but since my
application is only ~75% finished, it's still pretty important to have human
readable/editable tables.
Thanks,
Peter Darley
-----Original Message-----
From: Manfred Koizar [mailto:mkoi-pg(at)aon(dot)at]
Sent: Monday, January 31, 2005 3:06 AM
To: Peter Darley
Cc: Richard Huxton; Pgsql-Performance
Subject: Re: [PERFORM] Possibly slow query
On Wed, 26 Jan 2005 07:16:25 -0800, "Peter Darley"
<pdarley(at)kinesis-cem(dot)com> wrote:
>SELECT User_ID
>FROM Assignments A NATURAL LEFT JOIN (SELECT * FROM Assignment_Settings
>WHERE Setting='Status') ASet
>WHERE A.User_ID IS NOT NULL
> AND ASet.Assignment_ID IS NULL
>GROUP BY User_ID;
"ASet.Assignment_ID IS NULL" and "value IS NULL" as you had in your
original post don't necessarily result in the same set of rows.
SELECT DISTINCT a.User_ID
FROM Assignments a
LEFT JOIN Assignment_Settings s
ON (a.Assignment_ID=s.Assignment_ID
AND s.Setting='Status')
WHERE a.User_ID IS NOT NULL
AND s.Value IS NULL;
Note how the join condition can contain subexpressions that only depend
on columns from one table.
BTW,
|neo=# \d assignment_settings
| [...]
| setting | character varying(250) | not null
| [...]
|Indexes:
| [...]
| "assignment_settings_assignment_id_setting" unique, btree
(assignment_id, setting)
storing the setting names in their own table and referencing them by id
might speed up some queries (and slow down others). Certainly worth a
try ...
Servus
Manfred
From | Date | Subject | |
---|---|---|---|
Next Message | Olivier Sirven | 2005-01-31 15:16:07 | Re: PostgreSQL clustering VS MySQL clustering |
Previous Message | Markus Schaber | 2005-01-31 14:54:10 | Re: Automagic tuning |