From: | Christophe Pettus <pettus(at)postdirect(dot)com> |
---|---|
To: | pgsql-general(at)postgreSQL(dot)org |
Subject: | Expensive query |
Date: | 1998-10-30 19:50:28 |
Message-ID: | 4.1.19981030114400.00a1a810@exchange.postdirect.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have two tables, structured as:
users:
id varchar(70) not null unique
events:
userid varchar(70) not null,
code char(10) not null,
when datetime not null
The query I need to perform answers the question, "Which users do NOT
have a particular event (selected by code), and which do not have ANY
event for the last day?" The query I use is:
select id from users
where
id not in (
select unique id from events
where code = 'some code'
)
and
id not in (
select unique id from events
where age('now',when) <= '1 day'
);
This query is *very* expensive. With 10,000 users and 40,000 events,
it can take up to 20 minutes (!) to execute, and the postgres process
grows to 40 megabytes (!!) of memory (on a Pentium II system running
BSDI). This seems surprising, since each of the subqueries only needs
to be evaluated once, rather than once per row of users. Is there a
way to reformulate this query to make it less expensive?
From | Date | Subject | |
---|---|---|---|
Next Message | Dustin Sallings | 1998-10-30 22:13:12 | Re: [GENERAL] Expensive query |
Previous Message | Mike Meyer | 1998-10-30 18:08:28 | Transaction aborted? |