Skip site navigation (1) Skip section navigation (2)

Re: Possibly slow query

From: Manfred Koizar <mkoi-pg(at)aon(dot)at>
To: "Peter Darley" <pdarley(at)kinesis-cem(dot)com>
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 11:06:01
Message-ID: j44sv0hjeg0539tqriuppfo7o4och2dslc@email.aon.at (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Markus SchaberDate: 2005-01-31 14:54:10
Subject: Re: Automagic tuning
Previous:From: N SDate: 2005-01-31 09:38:36
Subject: Re: Postgres server getting slow!!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group