PostgreSQL查询速度极慢

Question

I have a table in a PostgreSQL database : temp with 4 columns

(
    id int, 
    campaign character varying, 
    sender character varying, 
    date_s date
) 

with around 9 millions records already. There is no indexes for now.

The problem is when I am trying to do a :

SELECT COUNT(*) 
FROM temp 
WHERE 
    id = $idmail and 
    campaign = '$variable_campaign' AND 
    date > '$date_c' "

in a 100K loop.

The query is not responding. (I have put a unlimited set_time_limit in PHP otherwise I'll get a 500 error under 5 minutes)

Actually the purpose of all this queries is to get a list of mails to which the concerned campaign was not sent in the current week.

Have you got any ideas please because I am really don't know how to do !

I can do a kind of temporary files if queries cannot be executed but I prefer deal with databases, it's cleaner !

Answer 1

Supposing there is a mails table with the id primary key:

select id    
from
    temp t
    right join
    mails m using(id)
where 
    t.campaign = '$variable_campaign' and
    t.date > '$date_c' and
    t.id is null

It will return all not sent mail ids.