Expensive query
От | Christophe Pettus |
---|---|
Тема | Expensive query |
Дата | |
Msg-id | 4.1.19981030114400.00a1a810@exchange.postdirect.com обсуждение исходный текст |
Ответы |
Re: [GENERAL] Expensive query
|
Список | 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?
В списке pgsql-general по дате отправления: