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

Possibly slow query

From: "Peter Darley" <pdarley(at)kinesis-cem(dot)com>
To: "Pgsql-Performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Possibly slow query
Date: 2005-01-26 00:19:42
Message-ID: PDEOIIFFBIAABMGNJAGPGEPJDLAA.pdarley@kinesis-cem.com (view raw or flat)
Thread:
Lists: pgsql-performance
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;

	The tables and an explain analyze of the query are as follows:

neo=# \d assignments;
                                               Table "shopper.assignments"
    Column     |              Type              |
Modifiers
---------------+--------------------------------+---------------------------
----------------------------------------------
 assignment_id | integer                        | not null default
nextval('shopper.assignments_assignment_id_seq'::text)
 sample_id     | integer                        | not null
 user_id       | integer                        |
 time          | timestamp(0) without time zone | not null default now()
 address_id    | integer                        |
Indexes:
    "assignments_pkey" primary key, btree (assignment_id)
    "assignments_sample_id" unique, btree (sample_id)
    "assignments_address_id" btree (address_id)
    "assignments_user_id" btree (user_id)
Triggers:
    assignments_check_assignment BEFORE INSERT ON assignments FOR EACH ROW
EXECUTE PROCEDURE check_assignment()

neo=# \d assignment_settings
                                                   Table
"shopper.assignment_settings"
        Column         |          Type          |
Modifiers
-----------------------+------------------------+---------------------------
--------------------------------------------------------------
 assignment_setting_id | integer                | not null default
nextval('shopper.assignment_settings_assignment_setting_id_seq'::text)
 assignment_id         | integer                | not null
 setting               | character varying(250) | not null
 value                 | text                   |
Indexes:
    "assignment_settings_pkey" primary key, btree (assignment_setting_id)
    "assignment_settings_assignment_id_setting" unique, btree
(assignment_id, setting)

neo=# explain analyze SELECT User_ID FROM Assignments A WHERE A.User_ID IS
NOT NULL AND (SELECT Value FROM Assignment_Settings WHERE Setti
ng='Status' AND Assignment_ID=A.Assignment_ID) IS NULL GROUP BY User_ID;
                                                         QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------
 HashAggregate  (cost=1.01..1.01 rows=1 width=4) (actual time=0.057..0.058
rows=1 loops=1)
   ->  Seq Scan on assignments a  (cost=0.00..1.01 rows=1 width=4) (actual
time=0.033..0.040 rows=2 loops=1)
         Filter: ((user_id IS NOT NULL) AND ((subplan) IS NULL))
         SubPlan
           ->  Seq Scan on assignment_settings  (cost=0.00..0.00 rows=1
width=13) (actual time=0.001..0.001 rows=0 loops=2)
                 Filter: (((setting)::text = 'Status'::text) AND
(assignment_id = $0))
 Total runtime: 0.159 ms
(7 rows)


	Thanks in advance for any help!

Thanks,
Peter Darley


Responses

pgsql-performance by date

Next:From: Tatsuo IshiiDate: 2005-01-26 01:09:04
Subject: Re: PgPool changes WAS: PostgreSQL clustering VS MySQL
Previous:From: Steve PoeDate: 2005-01-25 23:52:56
Subject: Re: Ideal disk setup for Postgresql 7.4?

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